Databases Reference
In-Depth Information
How to get IDs for all event classes and data columns?
ID values for required event classes and data columns must be passed to
the stored procedure
sp_trace_setevent
. You can get a list of EventIDs
for all event classes by querying
sys.trace_events
system catalog view.
To get a list of column IDs for all data columns, use
sys.trace_columns
system catalog view. Also, you can retrieve list of column IDs for all available
columns for a given event by querying
sys.trace_event_bindings
system catalog view and by joining it with
sys.trace_events
and
sys.
trace_columns
system catalog views on
trace_event_id
and
trace_
column_id
columns respectively.
The value of
@
on parameter value can be either
0
or
1
where the value
1
means that event
data for specified event class and data column should be captured otherwise not.
After adding the required event classes and data columns, the stored procedure
sp_trace_setstatus
is used to set the status of the trace to
START
. Any trace that
is created with system stored procedure is always in
STOP
state by default, and needs
to be started explicitly by calling
sp_trace_setstatus
stored procedure. This stored
procedure accepts the following parameters:
F
@traceid
F
@status
@TraceID
is the ID of the trace we created and need to be started.
@Status
specifies the
state of the trace. Possible values for
@Status
parameter are as follows:
F
0
: Stops a trace
F
1
: Starts a trace
F
2
: Closes a trace
Because we wanted to start our trace, we are passing a value of
1
to this parameter.
SQL Server keeps track of currently opened trace sessions. This list of traces can be retrieved
by querying
sys.traces
system catalog view. We just make sure by querying this view that
the trace is indeed created.
Next, we create a sample database named
SampleDBTrace
. We deliberately keep the
value of
FILEGROWTH
attribute smaller in order to be able to produce
Data
File
Auto
Growth
and
Log
File
Auto
Growth
events. The script also creates a sample table named
tbl_SampleData
though
SELECT
…
INTO
statement in which we insert one million sample
records by cross joining
sys.columns
system catalog view with itself multiple times. This
operation requires additional space in data and log files to make room for inserting new
records. For this, SQL Server has to increase the size of data and log files when required by
one MB (specified value for the
FILEGROWTH
attribute). This causes
DataFileAutoGrowth
and
LogFileAutoGrowth
events to be raised.
Search WWH ::
Custom Search