Skip to content

Instantly share code, notes, and snippets.

@lionofdezert
Created August 30, 2012 17:19
Show Gist options
  • Save lionofdezert/3534029 to your computer and use it in GitHub Desktop.
Save lionofdezert/3534029 to your computer and use it in GitHub Desktop.
Create a trace through TSQL and save it to a file
/***************************************************
Purpose: To create a trace and saving it to a file
Created by: AASIM ABDULLAH
For http://connectsql.blogspot.com
Date: 08/29/2012
****************************************************/
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
DECLARE @DateTime DATETIME
DECLARE @FileName NVARCHAR(400)
--Dynamic file name with full path
-- File name with path should be like D:\MyDB_AutoTraceAug302012
SELECT @FileName = N'D:\MyDB_AutoTrace' + REPLACE(LEFT(CONVERT(VARCHAR(50),GETDATE(),109),11),' ','')
--Set @DateTime value for single time use only
--set @DateTime = '2012-04-24 11:10:00.000' --Close trace at
SET @maxfilesize = 300 -- maximum file size in MBs
EXEC @rc = sp_trace_create @TraceID OUTPUT, 0,@FileName, @maxfilesize, NULL
IF (@rc != 0) GOTO error
-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent @TraceID, 10, 7, @on
EXEC sp_trace_setevent @TraceID, 10, 15, @on
EXEC sp_trace_setevent @TraceID, 10, 31, @on
EXEC sp_trace_setevent @TraceID, 10, 8, @on
EXEC sp_trace_setevent @TraceID, 10, 16, @on
EXEC sp_trace_setevent @TraceID, 10, 48, @on
EXEC sp_trace_setevent @TraceID, 10, 64, @on
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 17, @on
EXEC sp_trace_setevent @TraceID, 10, 41, @on
EXEC sp_trace_setevent @TraceID, 10, 49, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on
EXEC sp_trace_setevent @TraceID, 10, 26, @on
EXEC sp_trace_setevent @TraceID, 10, 34, @on
EXEC sp_trace_setevent @TraceID, 10, 50, @on
EXEC sp_trace_setevent @TraceID, 10, 3, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 35, @on
EXEC sp_trace_setevent @TraceID, 10, 51, @on
EXEC sp_trace_setevent @TraceID, 10, 4, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 60, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 10, 6, @on
EXEC sp_trace_setevent @TraceID, 10, 14, @on
-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT
-- Set filter for duration column, as we need only transaction with duration greater then 5
SET @bigintfilter = 5000000 -- Transaction taking more then 5 sec
EXEC sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1
-- display trace id for future references
SELECT TraceID=@TraceID
GOTO finish
error:
SELECT ErrorCode=@rc
finish:
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment