-------------------------------------------------------------------------------------- -- Because the SQL Profiler tool places a severe load on the production Database */ -- use this Script to start lightweight server-side trace for the following events: */ -- 10 RPC:Completed */ -- 12 SQL:BatchCompleted */ -- 45 SP:StmtCompleted */ -- 71 TSQL:PrepareSQL */ -- 72 TSQL:Exec PreparedSQL */ -- 73 TSQL:Unprepare SQL */ -- 123 SQL - Full Text Query */ -- 13 SQL:BatchStarting */ -- 97 Showplan All */ -- 137 Blocked Process Report */ -- */ -- set @TimeToRun -- Number of Minutes to Run */ -- set @maxfilesize -- Size of File */ -- set @TraceFileName -- Path and file name */ -- */ -- */ -- Replace the text 'InsertFileNameHere' with the name of your tracefile. */ -- */ -- This script returns the traceId that you will need to check the trace status. */ -- and to manually stop the trace. By default, trace will run for 15 minutes. */ -- To stop the trace manually, Use: */ -- exec sp_trace_setstatus @traceId, 2 */ -------------------------------------------------------------------------------------- declare @TimeToRun int set @TimeToRun = 1 -- @TimeToRun is duration of trace, in minutes declare @TraceFileName NVARCHAR(1000) set @TraceFileName = N'C:\Trace1' -- @TraceFileName set the path and file name of trace. declare @maxfilesize bigint set @maxfilesize = 50000 -- max trace file size in MB declare @rc int declare @TraceID int declare @optionValue int set @optionValue = 2 -- optionValue = 2 Specifies that when the @maxfilesize is reached, the current trace file is closed and a new file is created declare @stoptime datetime set @stoptime = dateadd(minute,@TimeToRun, getdate()) -- set @stoptime to NULL to run trace until stopped manually -- INSTRUCTIONS: -- Replace the text 'InsertFileNameHere', with an appropriate filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc -- extension will be appended to the filename automatically. -- This script returns the trace_id that you will need to check the trace status, and to stop the trace. -- sp_trace_create trace_id OUTPUT, option_value, 'trace_file', max_file_size, stop_time exec @rc = sp_trace_create @TraceID output, @optionValue,@TraceFileName, @maxfilesize, @stoptime -- set @stoptime to NULL to run trace forever, until stopped manually with 'exec sp_trace_setstatus @traceId, 2' if (@rc != 0) goto error -- Set the events, and the desired columns to be captured for each event declare @on bit set @on = 1 -- 10 RPC:Completed exec sp_trace_setevent @TraceID, 10, 1, @on exec sp_trace_setevent @TraceID, 10, 3, @on exec sp_trace_setevent @TraceID, 10, 4, @on exec sp_trace_setevent @TraceID, 10, 8, @on exec sp_trace_setevent @TraceID, 10, 12, @on exec sp_trace_setevent @TraceID, 10, 13, @on exec sp_trace_setevent @TraceID, 10, 14, @on exec sp_trace_setevent @TraceID, 10, 15, @on exec sp_trace_setevent @TraceID, 10, 16, @on exec sp_trace_setevent @TraceID, 10, 17, @on exec sp_trace_setevent @TraceID, 10, 18, @on exec sp_trace_setevent @TraceID, 10, 31, @on exec sp_trace_setevent @TraceID, 10, 34, @on exec sp_trace_setevent @TraceID, 10, 48, @on exec sp_trace_setevent @TraceID, 10, 50, @on exec sp_trace_setevent @TraceID, 10, 51, @on -- 12 SQL:BatchCompleted exec sp_trace_setevent @TraceID, 12, 1, @on exec sp_trace_setevent @TraceID, 12, 3, @on exec sp_trace_setevent @TraceID, 12, 4, @on exec sp_trace_setevent @TraceID, 12, 8, @on exec sp_trace_setevent @TraceID, 12, 12, @on exec sp_trace_setevent @TraceID, 12, 13, @on exec sp_trace_setevent @TraceID, 12, 14, @on exec sp_trace_setevent @TraceID, 12, 15, @on exec sp_trace_setevent @TraceID, 12, 16, @on exec sp_trace_setevent @TraceID, 12, 17, @on exec sp_trace_setevent @TraceID, 12, 18, @on exec sp_trace_setevent @TraceID, 12, 31, @on exec sp_trace_setevent @TraceID, 12, 48, @on exec sp_trace_setevent @TraceID, 12, 50, @on exec sp_trace_setevent @TraceID, 12, 51, @on -- 13 SQL:BatchStarting exec sp_trace_setevent @TraceID, 13, 1, @on exec sp_trace_setevent @TraceID, 13, 3, @on exec sp_trace_setevent @TraceID, 13, 4, @on exec sp_trace_setevent @TraceID, 13, 8, @on exec sp_trace_setevent @TraceID, 13, 10, @on exec sp_trace_setevent @TraceID, 13, 12, @on exec sp_trace_setevent @TraceID, 13, 14, @on exec sp_trace_setevent @TraceID, 13, 27, @on exec sp_trace_setevent @TraceID, 13, 35, @on exec sp_trace_setevent @TraceID, 13, 49, @on exec sp_trace_setevent @TraceID, 13, 50, @on exec sp_trace_setevent @TraceID, 13, 51, @on exec sp_trace_setevent @TraceID, 13, 66, @on -- 45 SP:StmtCompleted exec sp_trace_setevent @TraceID, 45, 1, @on exec sp_trace_setevent @TraceID, 45, 3, @on exec sp_trace_setevent @TraceID, 45, 4, @on exec sp_trace_setevent @TraceID, 45, 5, @on exec sp_trace_setevent @TraceID, 45, 8, @on exec sp_trace_setevent @TraceID, 45, 12, @on exec sp_trace_setevent @TraceID, 45, 13, @on exec sp_trace_setevent @TraceID, 45, 14, @on exec sp_trace_setevent @TraceID, 45, 15, @on exec sp_trace_setevent @TraceID, 45, 16, @on exec sp_trace_setevent @TraceID, 45, 17, @on exec sp_trace_setevent @TraceID, 45, 18, @on exec sp_trace_setevent @TraceID, 45, 25, @on exec sp_trace_setevent @TraceID, 45, 29, @on exec sp_trace_setevent @TraceID, 45, 34, @on exec sp_trace_setevent @TraceID, 45, 48, @on exec sp_trace_setevent @TraceID, 45, 50, @on exec sp_trace_setevent @TraceID, 45, 51, @on exec sp_trace_setevent @TraceID, 45, 55, @on -- 71 TSQL:PrepareSQL exec sp_trace_setevent @TraceID, 71, 3, @on exec sp_trace_setevent @TraceID, 71, 4, @on exec sp_trace_setevent @TraceID, 71, 8, @on exec sp_trace_setevent @TraceID, 71, 12, @on exec sp_trace_setevent @TraceID, 71, 14, @on exec sp_trace_setevent @TraceID, 71, 33, @on exec sp_trace_setevent @TraceID, 71, 50, @on exec sp_trace_setevent @TraceID, 71, 51, @on -- 72 TSQL:Exec PreparedSQL exec sp_trace_setevent @TraceID, 72, 3, @on exec sp_trace_setevent @TraceID, 72, 4, @on exec sp_trace_setevent @TraceID, 72, 8, @on exec sp_trace_setevent @TraceID, 72, 12, @on exec sp_trace_setevent @TraceID, 72, 14, @on exec sp_trace_setevent @TraceID, 72, 33, @on exec sp_trace_setevent @TraceID, 72, 50, @on exec sp_trace_setevent @TraceID, 72, 51, @on -- 73 TSQL:Unprepare SQL exec sp_trace_setevent @TraceID, 73, 3, @on exec sp_trace_setevent @TraceID, 73, 4, @on exec sp_trace_setevent @TraceID, 73, 8, @on exec sp_trace_setevent @TraceID, 73, 12, @on exec sp_trace_setevent @TraceID, 73, 14, @on exec sp_trace_setevent @TraceID, 73, 33, @on exec sp_trace_setevent @TraceID, 73, 50, @on exec sp_trace_setevent @TraceID, 73, 51, @on -- 123 SQL - Full Text Query exec sp_trace_setevent @TraceID, 123, 1, @on exec sp_trace_setevent @TraceID, 123, 3, @on exec sp_trace_setevent @TraceID, 123, 4, @on exec sp_trace_setevent @TraceID, 123, 5, @on exec sp_trace_setevent @TraceID, 123, 8, @on exec sp_trace_setevent @TraceID, 123, 12, @on exec sp_trace_setevent @TraceID, 123, 13, @on exec sp_trace_setevent @TraceID, 123, 14, @on exec sp_trace_setevent @TraceID, 123, 15, @on exec sp_trace_setevent @TraceID, 123, 16, @on exec sp_trace_setevent @TraceID, 123, 17, @on exec sp_trace_setevent @TraceID, 123, 18, @on exec sp_trace_setevent @TraceID, 123, 25, @on exec sp_trace_setevent @TraceID, 123, 29, @on exec sp_trace_setevent @TraceID, 123, 32, @on exec sp_trace_setevent @TraceID, 123, 34, @on exec sp_trace_setevent @TraceID, 123, 48, @on exec sp_trace_setevent @TraceID, 123, 50, @on exec sp_trace_setevent @TraceID, 123, 51, @on exec sp_trace_setevent @TraceID, 123, 55, @on -- 97 Showplan XML Statistics Profile exec sp_trace_setevent @TraceID, 97, 1, @on exec sp_trace_setevent @TraceID, 97, 2, @on exec sp_trace_setevent @TraceID, 97, 4, @on exec sp_trace_setevent @TraceID, 97, 5, @on exec sp_trace_setevent @TraceID, 97, 8, @on exec sp_trace_setevent @TraceID, 97, 12, @on exec sp_trace_setevent @TraceID, 97, 14, @on exec sp_trace_setevent @TraceID, 97, 25, @on exec sp_trace_setevent @TraceID, 97, 27, @on exec sp_trace_setevent @TraceID, 97, 28, @on exec sp_trace_setevent @TraceID, 97, 29, @on exec sp_trace_setevent @TraceID, 97, 34, @on exec sp_trace_setevent @TraceID, 97, 35, @on exec sp_trace_setevent @TraceID, 97, 41, @on exec sp_trace_setevent @TraceID, 97, 49, @on exec sp_trace_setevent @TraceID, 97, 51, @on exec sp_trace_setevent @TraceID, 97, 66, @on -- 137 Blocked Process Report exec sp_trace_setevent @TraceID, 137, 1, @on exec sp_trace_setevent @TraceID, 137, 3, @on exec sp_trace_setevent @TraceID, 137, 4, @on exec sp_trace_setevent @TraceID, 137, 13, @on exec sp_trace_setevent @TraceID, 137, 15, @on exec sp_trace_setevent @TraceID, 137, 22, @on exec sp_trace_setevent @TraceID, 137, 24, @on exec sp_trace_setevent @TraceID, 137, 26, @on exec sp_trace_setevent @TraceID, 137, 27, @on exec sp_trace_setevent @TraceID, 137, 32, @on exec sp_trace_setevent @TraceID, 137, 41, @on exec sp_trace_setevent @TraceID, 137, 51, @on exec sp_trace_setevent @TraceID, 137, 60, @on exec sp_trace_setevent @TraceID, 137, 64, @on -- 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