Create a trace through TSQL and save it to a file

AutoTrace.sql

/***************************************************
 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