Creating Custom TRACE


Traces allow you to track the specific actions performed against a SQL Server databases. They provide valuable information for troubleshooting database issues and tuning database engine performance. For example, TRACE can be used to monitor the slow running queries/ SPs as well as poor performing queries or SPs. In this tutorial, we walk through the process of creating a custom SQL Server Trace with T-SQL by defining events, columns & filters.

The very first step in creating the custom TRACE is to create the TRACE file at specific location with events. This step also contains Starting the Trace File. We will use the system stored procedure to create the custom TRACE.

 

DECLARE @trace_id INT
DECLARE @stop_time DATETIME 
DECLARE @max_filesize BIGINT
DECLARE @eventon BIT
DECLARE @trace_Path nvarchar(256) 
DECLARE @trace_file nvarchar(256)

 
--Value Assigment to Variables
SET @trace_Path   = 'C:\SQLTrace\'
SET @trace_file = 'Trace_' + CONVERT(VARCHAR(10), GETDATE(), 112)
 

----------------  Create New Trace File
SET @trace_file = @trace_Path + REPLACE(@trace_file, '.trc', '')
SET @stop_time = DATEADD(hh, 12, GETDATE())
SET @max_filesize = 8192
SET @eventon = 1

----------------  Create Trace
EXEC sp_trace_create 
      @traceid = @trace_id OUTPUT,
      @options = 4,
      @tracefile = @trace_file,
      @maxfilesize = @max_filesize,
      @stoptime = @stop_time 

----------------  Add Events
DECLARE @event_id INT 

------------------      RPC:Completed
SET @event_id = 10
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 1, @on = @eventon -- TextData
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 3, @on = @eventon -- DatabaseID
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 6, @on = @eventon -- NTUserName
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 7, @on = @eventon -- NTDomainName
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 10, @on = @eventon -- ApplicationName
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 13, @on = @eventon -- Duration
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 14, @on = @eventon -- StartTime
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 15,  @on = @eventon -- EndTime
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 16, @on = @eventon -- Reads
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 17, @on = @eventon -- Writes
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 18, @on = @eventon -- CPU
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 27, @on = @eventon -- EventClass
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 35, @on = @eventon -- DatabaseName
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 48, @on = @eventon -- Rowcounts

------------------      SQL:BatchCompleted
SET @event_id = 12
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 1, @on = @eventon -- TextData
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 3, @on = @eventon -- DatabaseID
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 6, @on = @eventon -- NTUserName
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 7, @on = @eventon -- NTDomainName
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 10, @on = @eventon -- ApplicationName
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 13, @on = @eventon -- Duration
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 14, @on = @eventon -- StartTime
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 15,  @on = @eventon -- EndTime
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 16, @on = @eventon -- Reads
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 17, @on = @eventon -- Writes
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 18, @on = @eventon -- CPU
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 27, @on = @eventon -- EventClass
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 35, @on = @eventon -- DatabaseName
EXEC sp_trace_setevent @traceid = @trace_id, @eventid = @event_id, @columnid = 48, @on = @eventon -- Rowcounts
 
----------------  Start Trace
EXEC sp_trace_setstatus @traceid = @trace_id, @status = 1

The above script will create the TRACE file at ‘C:\SQLTrace’ folder with file name “Trace_.”. You can also add the filters in the Trace using sp_trace_setfilter stored procedure.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s