Sp_start_trace

for RuBoard

SQL Server's Profiler utility is a powerful tool and a vast improvement over the SQL Trace facility that came with releases prior to 7.0. However, it's also a resource- intensive and sometimes painfully slow application. Because I often need to trace the activity on SQL Server in situations where I don't wish to incur the overhead of the Profiler GUI, I wrote my own set of stored procedures to manage Profiler traces. They allow you to start, stop, and list Profiler traces without actually using Profiler itself. How do they work? By calling SQL Server's sp_trace_% extended procedures, the same way that Profiler itself does. These procedures are documented in the Books Online and are considerably easier to use than previous versions.

My trace management code consists of three procedures: sp_start_trace, sp_stop_trace, and sp_list_trace. They each do what their names imply. Here's the code for sp_start_trace (Listing 21-4):

Listing 21-4 sp_start_trace.

 USE master GO IF OBJECT_ID('sp_start_trace') IS NOT NULL   DROP PROC sp_start_trace GO CREATE PROC sp_start_trace @FileName sysname=NULL, @TraceName sysname='tsqltrace', @Options int=2, @MaxFileSize bigint=5, @StopTime datetime=NULL, @Events varchar(300)= --  11 - RPC:Starting --  13 - SQL:BatchStarting --  14 - Connect --  15 - Disconnect --  16 - Attention --  17 - Existing Connection --  33 - Exception --  42 - SP:Starting --  43 - SP:Completed --  45 - SP:StmtCompleted --  55 - Hash Warning --  67 - Execution Warnings --  69 - Sort Warnings --  79 - Missing Column Statistics --  80 - Missing Join Predicate '11,13,14,15,16,17,33,42,43,45,55,67,69,79,80', @Cols varchar(300)= -- All columns '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,', @IncludeTextFilter sysname=NULL, @ExcludeTextFilter sysname=NULL, @IncludeObjIdFilter int=NULL, @ExcludeObjIdFilter int=NULL, @TraceId int = NULL AS SET NOCOUNT ON IF @FileName='/?' GOTO Help -- Declare variables DECLARE @OldQueueHandle int -- Queue handle of currently running trace queue DECLARE @QueueHandle int -- Queue handle for new running trace queue DECLARE @On bit -- Necessary because of a bug in some of the sp_trace_xx procs DECLARE @OurObjId int -- Used to keep us out of the trace log DECLARE @OldTraceFile sysname -- File name of running trace DECLARE @res int -- Result var for sp calls SET @On=1 -- Do some basic param validation IF (@Cols IS NULL) BEGIN   RAISERROR('You must specify the columns to trace.',16,10)   RETURN -1 END IF (@Events IS NULL) BEGIN   RAISERROR('You must specify a list of trace events in @Events.',16,10)   RETURN -1 END -- Append the datetime to the file name to create a new, unique file name. IF @FileName IS NULL  SELECT @FileName = 'c:\TEMP\tsqltrace_' + CONVERT(CHAR(8),getdate(),112) +    REPLACE(CONVERT(varchar(15),getdate(),114),':','') -- Create the trace queue   EXEC @res=sp_trace_create @traceid=@QueueHandle OUT, @options=@Options,     @tracefile=@FileName, @maxfilesize=@MaxFileSize, @stoptime=@StopTime IF @res<>0 BEGIN   IF @res=1 PRINT 'Trace not started. Reason: Unknown error.'   ELSE IF @res=10 PRINT 'Trace not started. Reason: Invalid options.Returned when options specified are incompatible.' ELSE IF @res=12 PRINT 'Trace not started. Reason: Error creating file. Returned if the file already exists, drive is out of space, or path does not exist.'   ELSE IF @res=13 PRINT 'Trace not started. Reason: Out of memory. Returned when there is not enough memory to perform the specified action.'   ELSE IF @res=14 PRINT 'Trace not started. Reason: Invalid stop time. Returned when the stop time specified has already happened.'   ELSE IF @res=15 PRINT 'Trace not started. Reason: Invalid parameters. Returned when the user supplied incompatible parameters.'   RETURN @res END PRINT 'Trace started.' PRINT 'The trace file name is : '+@FileName+'.' -- Specify the event classes and columns to trace IF @Events IS NOT NULL BEGIN -- Loop through the @Events and @Cols strings, parsing out each event & column number and adding them to the trace definition  IF RIGHT(@Events,1)<>',' SET @Events=@Events+',' -- Append comma for the loop   IF RIGHT(@Cols,1)<>',' SET @Cols=@Cols+',' -- Append comma for the loop   DECLARE @i int, @j int, @Event int, @Col int, @ColStr varchar(300)   SET @i=CHARINDEX(',',@Events)   WHILE @i<>0 BEGIN     SET @Event=CAST(LEFT(@Events,@i-1) AS int)     SET @ColStr=@Cols     SET @j=CHARINDEX(',',@ColStr)     WHILE @j<>0 BEGIN       SET @Col=CAST(LEFT(@ColStr,@j-1) AS int)       EXEC sp_trace_setevent @traceid=@QueueHandle, @eventid=@Event,         @columnid=@Col, @on=@On       SET @ColStr=SUBSTRING(@ColStr,@j+1,300)       SET @j=CHARINDEX(',',@ColStr)     END     SET @Events=SUBSTRING(@Events,@i+1,300)     SET @i=CHARINDEX(',',@Events)   END END -- Set filters (default values avoid tracing the trace activity itself) -- You can specify other filters like application name, etc., by supplying strings to the @IncludeTextFilter/@ExcludeTextFilter parameters, separated by semicolons SET @ExcludeTextFilter='sp_%trace%'+ISNULL(';'+@ExcludeTextFilter,'')   -- By default, keep our own activity from showing up SET @OurObjId=OBJECT_ID('master..sp_start_trace') EXEC sp_trace_setfilter @traceid=@QueueHandle, @columnid=1,    @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter EXEC sp_trace_setfilter @traceid=@QueueHandle, @columnid=1,    @logical_operator=0, @comparison_operator=7, @value=N'EXEC% sp_%trace%' IF @IncludeTextFilter IS NOT NULL    EXEC sp_trace_setfilter @traceid=@QueueHandle, @columnid=1,    @logical_operator=0, @comparison_operator=6, @value=@IncludeTextFilter IF @IncludeObjIdFilter IS NOT NULL    EXEC sp_trace_setfilter @traceid=@QueueHandle, @columnid=22,    @logical_operator=0, @comparison_operator=0, @value=@IncludeObjIdFilter    EXEC sp_trace_setfilter @traceid=@QueueHandle, @columnid=22,    @logical_operator=0, @comparison_operator=1, @value=@OurObjId IF @ExcludeObjIdFilter IS NOT NULL    EXEC sp_trace_setfilter @traceid=@QueueHandle, @columnid=22,    @logical_operator=0, @comparison_operator=1, @value=@ExcludeObjIdFilter -- Turn on the trace EXEC sp_trace_setstatus @traceid=@QueueHandle, @status=1 -- Record the trace queue handle for subsequent jobs. (This allows us to know how to stop the trace.) IF OBJECT_ID('tempdb..TraceQueue') IS NULL BEGIN   CREATE TABLE tempdb..TraceQueue   (TraceID int, TraceName varchar(20), TraceFile sysname)   INSERT tempdb..TraceQueue VALUES(@QueueHandle, @TraceName, @FileName) END ELSE BEGIN  IF EXISTS(SELECT * FROM tempdb..TraceQueue WHERE TraceName = @TraceName) BEGIN    UPDATE tempdb..TraceQueue SET TraceID = @QueueHandle, TraceFile=@FileName    WHERE TraceName = @TraceName   END ELSE BEGIN     INSERT tempdb..TraceQueue VALUES(@QueueHandle, @TraceName, @FileName)   END END RETURN 0 Help: /* Code abridged */ RETURN -1 GO exec sp_start_trace 

(Results)

 Trace started. The trace file name is : c:\TEMP\tsqltrace_20001204011454350. 

By default, sp_start_trace initiates a Profiler trace that includes all trace columns and a stock set of trace events, but you can change this by specifying the @Events and/or @Cols parameters. Both are comma-delimited lists of event/column numbers. You can look up these numbers in the Books Online.

If you don't specify your own trace output file name, sp_start_trace attempts to create one for you in C:\TEMP using the current date and time to label the file. Trace files can grow to be quite large, so you should be careful to direct the file to a drive where you have plenty of space. Understand that the path that sp_start_trace uses is relative to the SQL Server on which the trace will run, not your local machine. Unlike Profiler, sp_start_trace always runs traces exclusively on the server.

for RuBoard