- Defining a trace.
- Defining the events and columns to captured.
- Defining the filter condition.
- Start the trace.
/**************************************************************************************** STEP 1 : DEFINING THE TRACE ***************************************************************************************/ SET NOCOUNT ON; DECLARE @rc INT DECLARE @TraceID INT DECLARE @MaxFileSize BIGINT
DECLARE @OutputFileName NVARCHAR(256) SET @MaxFileSize = 1024
--Replace The H:\MyTraces with a valid folder in your environment
SET @OutputFileName = 'D:\MyTraces\FileTrace' + CONVERT(VARCHAR(20), GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')
--sp_trace_create @traceid,@options,@tracefile,@maxfilesize,@stoptime ,@filecount EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, @OutputFileName, @MaxFileSize, NULL,5
/**************************************************************************************** STEP 2 : DEFINING THE EVENT AND COLUMNS *****************************************************************************************/
DECLARE @Status bit SET @Status = 1
--sp_trace_setevent @traceid ,@eventid ,@columnid,@on
--RPC:Completed event
EXEC sp_trace_setevent @TraceID, 10, 16, @Status EXEC sp_trace_setevent @TraceID, 10, 1, @Status EXEC sp_trace_setevent @TraceID, 10, 17, @Status EXEC sp_trace_setevent @TraceID, 10, 14, @Status EXEC sp_trace_setevent @TraceID, 10, 18, @Status EXEC sp_trace_setevent @TraceID, 10, 12, @Status EXEC sp_trace_setevent @TraceID, 10, 13, @Status EXEC sp_trace_setevent @TraceID, 10, 8, @Status EXEC sp_trace_setevent @TraceID, 10, 10, @Status EXEC sp_trace_setevent @TraceID, 10, 11, @Status EXEC sp_trace_setevent @TraceID, 10, 35, @Status
--SQL:BatchCompleted event
EXEC sp_trace_setevent @TraceID, 12, 16, @Status EXEC sp_trace_setevent @TraceID, 12, 1, @Status EXEC sp_trace_setevent @TraceID, 12, 17, @Status EXEC sp_trace_setevent @TraceID, 12, 14, @Status EXEC sp_trace_setevent @TraceID, 12, 18, @Status EXEC sp_trace_setevent @TraceID, 12, 12, @Status EXEC sp_trace_setevent @TraceID, 12, 13, @Status EXEC sp_trace_setevent @TraceID, 12, 8, @Status EXEC sp_trace_setevent @TraceID, 12, 10, @Status EXEC sp_trace_setevent @TraceID, 12, 11, @Status EXEC sp_trace_setevent @TraceID, 12, 35, @Status
/**************************************************************************************** STEP 3 : DEFINING THE Filter condition *****************************************************************************************/ --sp_trace_setfilter @traceid ,@columnid,@logical_operator,@comparison_operator,@value EXEC sp_trace_setfilter @TraceID,8,0,0,N'MyAppServer' --Hostname EXEC sp_trace_setfilter @TraceID,35,0,0,N'MyDB' --Database name EXEC sp_trace_setfilter @TraceID,11,0,0,N'MyAppUser' --SQL login /**************************************************************************************** STEP 4 : Start the trace *****************************************************************************************/ EXEC sp_trace_setstatus @TraceID, 1 /**************************************************************************************** Display the trace Id and traceFilename *****************************************************************************************/ SELECT @TraceID,@OutputFileName
SELECT * FROM ::fn_trace_getinfo(NULL)
Once it ran for the desired time , you can stop the trace using the below script
--sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
DECLARE @traceid INT DECLARE @status INT SET @traceid =2 SET @status =0 EXEC sp_trace_setstatus @traceid,@status SET @status =2 EXEC sp_trace_setstatus @traceid,@status
To view the content of the trace file. If you have added more column in the trace , add that column in the below select statement also.You can insert the output into a table for further analysis of the trace.
SELECT
TextData, Duration/1000, Reads, Writes, CPU, StartTime,HostName,ApplicationName,LoginName,DatabaseName
FROM fn_trace_gettable('D:\MyTraces\FileTrace20120929023408.trc',1)
Below query will help us to list the events and columns capturing as part of a trace.
SELECT t.EventID, t.ColumnID, e.name AS Event_Description, c.name AS Column_DescriptionFROM ::fn_trace_geteventinfo(2) t --Change the trace id to appropriate one JOIN sys.trace_events e ON t.eventID = e.trace_event_idJOIN sys.trace_columns c ON t.columnid = c.trace_column_id
If you liked this post, do like my page on FaceBook
