SQL Trace provides a set of stored procedures to create traces on an instance of the SQL Server Database Engine. Through SQL Trace We can monitor events on SQL server without using SQL Profiler. Using SQL Trace we can find out who is using the database server, from which workstation or server, and which application is using it.
SQL Trace is a performance tuning tool which is used to fine tune your program. It traces the time taken to fetch the data from the database and helps us to reduce it. While starting the SQL Trace, we can set the predefined time at which time we want to stop the trace, or we can specify a predefined size where we can stop the trace.
We can stop and restart the trace at anytime. Traces must be restarted every time the server restarts.
The following select statement shows the existing sql traces on the server.
SELECT * FROM sys.traces
Creating a Trace in T-SQL:
Syntax:
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
[ , [ @filecount = ] 'max_rollover_files' ]
Note:
trace_id – trace id assigned by the sql server, which is an output parameter.
option_value – represents the options set.
trace_file - represents the trace file location.
max_file_size - by using this we can specify the maximum size of the trace file.
stop_time - represents the stop time for trace.
DECLARE @Return int, @TraceID int, @on BIT
EXEC @Return = sp_trace_create @TraceID output, 0, N'C:\SampleTrace'
In the above script @TraceID as output parameter and 0 is the stauts of the trace, 0 indicates the stopped state.
Populate the Trace File in T-SQL:
Use the sp_trace_setevent procedure to populate the trace file.
Syntax:
sp_trace_setevent [ @traceid = ] trace_id
, [ @eventid = ] event_id
, [ @columnid = ] column_id
, [ @on = ] on
Use the scripts:
DECLARE @Return int, @TraceID int, @on BIT
EXEC @Return = sp_trace_create @TraceID output, 0, N'C:\SampleTrace'
SELECT @Return AS ReturnVal, @TraceID AS TraceID
/**the above line of script is used to select the Trace ID and return value,
which checks the success of the trace creation.**/
SELECT @on = 1
EXEC sp_trace_setevent @TraceID, 13, 1, @on
/** Here 13 (event_id) represents the SQL:BatchStarting
1 (column_id) represents TextData (the text value captured in the trace)
Start the Trace by setting the status as 1 **/
EXEC @Return = sp_trace_setstatus @TraceID, 1
Result:
The trace file will be created at C: with name SampleTrace:
If we open the file, it looks like the following image:
Refer to MSDN for the list of Event and Column details ( http://msdn.microsoft.com/en-us/library/ms186265.aspx ).
Set a Trace Filter in T-SQL:
We can add filters for the traced events.
Syntax:
sp_trace_setfilter [ @traceid = ] trace_id
, [ @columnid = ] column_id
, [ @logical_operator = ] logical_operator
, [ @comparison_operator = ] comparison_operator
, [ @value = ] value
Note:
trace_id - trace id assigned by the sql server while creating the trace.
column_id – filter applied column id value.
logical_operator - 0 represents AND , 1 represents OR. This toggles between different filters.
comparison_operator - represents the type of comparion.
The numbers 0, 1,2,3,4,5,6 and 7 represents the operators like = (equal), <> (not equal), > (greater then), < (less than), >= (greater than or equal to), <= (less than or equal to), LIKE and NOT LIKE.
Example:
Consider a situation, You want to trace the SQL:BatchString which is executed by the Login “admin”, and you want to ignore the others.
Use the scripts:
DECLARE @Return int, @TraceID int, @on BIT
EXEC @Return = sp_trace_create @TraceID output, 0, N'C:\SampleTrace'
SELECT @Return AS ReturnVal, @TraceID AS TraceID
SELECT @on = 1
EXEC sp_trace_setevent @TraceID, 13, 1, @on
EXEC sp_trace_setfilter @TraceID, 11, 0, 0, N'admin'
EXEC @Return = sp_trace_setstatus @TraceID, 1
SELECT @Return AS ReturnVal, @TraceID AS TraceID
For login’s other than admin the trace file will result like the following image,
Delete a Trace in T-SQL:
We must stop the status of the trace first, and then only we can modify or delete the trace. By using the sp_trace_setstatus procedure we can delete the trace.
Syntax
sp_trace_setstatus [ @traceid= ] trace_id
, [ @status = ] status
Note
trace_id - trace id assigned by the sql server while creating the trace.
Status – status of the trace.
Use the script:
--This script stops the trace,
Execute sp_trace_setstatus @traceid,0
--This script closes the trace and deletes the trace definition from the server
Execute sp_trace_setstatus @traceid,2