Defining Server-Side Traces


Much of the SQL Server Profiler functionality is available through a set of system-stored procedures. Through these procedures, you can define a server-side trace that can be run automatically or on a scheduled basis, such as via a Task Manager Job, instead of through the Profiler GUI. Server-side traces are also useful if you are tracing information over an extended period of time, or are planning on capturing a large amount of trace information. The overhead of running a server-side trace is less than running a client-side trace with Profiler.

To start a server-side trace, you need to define the trace using the trace- related system procedures. These procedures can be called from within a SQL Serverstored procedure or batch. You can define a server-side trace using the following four procedures:

  • sp_trace_create This procedure performs similarly to the General tab in the Trace Properties dialog box. It sets up the trace and defines the file to store the captured events. sp trace create returns a trace ID number that you'll need to reference from the other three procedures to further define and manage the trace.

  • sp_trace_setevent You need to call this procedure once for each data column of every event that you want to capture.

  • sp_trace_setfilter Call this procedure once for each filter you want to define on any of the event data columns .

  • sp_trace_setstatus After the trace is defined, call this procedure to start, stop, or remove the trace. You must stop and remove a trace definition before you can open and view the trace file.

You will find that manually creating procedure scripts for tracing can be rather tedious . Much of the tedium is due to the fact that many numeric parameters drive the trace execution. Tables 7.2 and 7.3 provided the ID numbers you would need to specify to for the events and data columns you would want to include in your trace. In addition, you have to call the sp_trace_setevent procedure once for each data column for each event in the trace. To set up filters, you must pass the column ID, the filter value, and numeric values for the logical operator and the column operator to the sp_trace_setfilter procedure. The logical operator can be either 0 or 1. A value of 0 indicates that the specified filter on the column should be ANDed with any other filters on the column, while a value of 1 indicates the OR operator should be applied. Table 7.4 describes the values allowed for the column operators

Table 7.4. Column Operator Values for sp_trace_setfilter
Value Comparison Operator
= (Equal)
1 <> (Not Equal)
2 > (Greater Than)
3 < (Less Than)
4 >= (Greater Than Or Equal)
5 <= (Less Than Or Equal)
6 LIKE
7 NOT LIKE

Fortunately, there is an easier way of generating a trace definition script! You can set up your traces using the SQL Profiler GUI and script the trace definition to a file. Once you have the trace defined and have specified the events, data columns, and filters you want to use, select the Script Trace menu option from the File menu in Profiler to save the SQL commands to define and invoke the trace to a file. You have the option to generate a script for either SQL Server 7.0 or 2000. The script file generated contains all of the SQL, including the stored procedure executions that you will need to run to set up your server-side trace.

The SQL script generated by Profiler will only contain the commands to define the events and data columns that are contained in the Profiler trace it was generated from. If you prefer a SQL script template that is a bit more dynamic for generating your server-side traces, you can use the SQL script as shown in Listing 7.2. This script provides all the event and data column options available and lets you specify which ones to use by commenting or uncommenting out the appropriate lines. It also provides some good examples and more complete syntax for using the Profiler-related sp_trace stored procedures. A copy of this script is available on the accompanying CD.

Listing 7.2 A SQL Script for Creating and Starting a Server-Side Trace
 set nocount on go -- Declare needed variables DECLARE @Trace_Name varchar(255),         @strTraceDirectory nvarchar (1000),         @maxfilesize bigint,         @TraceID int ,         @rc int,         @strTraceFile nvarchar (245),         @tracefile_basename nvarchar(30),         @stoptime datetime -- Specify trace name, trace file directory, tracefile_basename, --  max file size in MB, and stop time for trace -- The trace directory is a directory on SQL Server machine -- If you want to write from a remote SQL Server to a local drive, --  or to a drive on another server, use UNC path and make sure SQL server has --  write access to the specified network share -- The tracefile_basename will be appended with a datetime stamp when --  the trace is run -- If stoptime if null, trace runs until manually stopped select @Trace_Name = 'Sample Trace',        @strTraceDirectory = 'c:\temp',        @maxfilesize = 100,        @tracefile_basename = 'sampletrace',        @stoptime = NULL -- Create table variables to hold desired events and columns declare @trace_events TABLE (eventid int) declare @trace_columns TABLE (columnid int) -- Specify which events to trace --  (uncomment insert statements for desired events) --insert @trace_events (eventid) values (10) -- RPC:Completed --insert @trace_events (eventid) values (11) -- RPC:Starting insert @trace_events (eventid) values (12) -- SQL:BatchCompleted --insert @trace_events (eventid) values (13) -- SQL:BatchStarting insert @trace_events (eventid) values (14) -- Login insert @trace_events (eventid) values (15) -- Logout insert @trace_events (eventid) values (16) -- Attention insert @trace_events (eventid) values (17) -- ExistingConnection --insert @trace_events (eventid) values (18) -- ServiceControl --insert @trace_events (eventid) values (19) -- DTCTransaction --insert @trace_events (eventid) values (20) -- Login Failed --insert @trace_events (eventid) values (21) -- EventLog --insert @trace_events (eventid) values (22) -- ErrorLog --insert @trace_events (eventid) values (23) -- Lock:Released --insert @trace_events (eventid) values (24) -- Lock:Acquired insert @trace_events (eventid) values (25) -- Lock:Deadlock --insert @trace_events (eventid) values (26) -- Lock:Cancel insert @trace_events (eventid) values (27) -- Lock:Timeout --insert @trace_events (eventid) values (28) -- Degree of Parallelism1 --insert @trace_events (eventid) values (29) -- Degree of Parallelism2 --insert @trace_events (eventid) values (30) -- Degree of Parallelism3 --insert @trace_events (eventid) values (31) -- Degree of Parallelism4 insert @trace_events (eventid) values (33) -- Exception --insert @trace_events (eventid) values (34) -- SP:CacheMiss --insert @trace_events (eventid) values (35) -- SP:CacheInsert --insert @trace_events (eventid) values (36) -- SP:CacheRemove --insert @trace_events (eventid) values (37) -- SP:Recompile --insert @trace_events (eventid) values (38) -- SP:CacheHit --insert @trace_events (eventid) values (39) -- SP:ExecContextHit --insert @trace_events (eventid) values (40) -- SQL:StmtStarting insert @trace_events (eventid) values (41) -- SQL:StmtCompleted insert @trace_events (eventid) values (42) -- SP:Starting insert @trace_events (eventid) values (43) -- SP:Completed --insert @trace_events (eventid) values (44) -- SP:Statement Starting insert @trace_events (eventid) values (45) -- SP:Statement Completed --insert @trace_events (eventid) values (46) -- Object:Created --insert @trace_events (eventid) values (47) -- Object:Deleted --insert @trace_events (eventid) values (48) -- Object:Opened --insert @trace_events (eventid) values (49) -- Object:Closed --insert @trace_events (eventid) values (50) -- SQL Transaction --insert @trace_events (eventid) values (51) -- Scan:Started --insert @trace_events (eventid) values (52) -- Scan:Stopped --insert @trace_events (eventid) values (53) -- CursorOpen --insert @trace_events (eventid) values (54) -- Transaction Log --insert @trace_events (eventid) values (55) -- Hash Warning insert @trace_events (eventid) values (58) -- Auto Stats insert @trace_events (eventid) values (59) -- Lock:Deadlock Chain --insert @trace_events (eventid) values (60) -- Lock:Escalation --insert @trace_events (eventid) values (61) -- OLE DB Errors --insert @trace_events (eventid) values (67) -- Execution Warnings --insert @trace_events (eventid) values (68) -- Execution Plan --insert @trace_events (eventid) values (69) -- Sort Warnings --insert @trace_events (eventid) values (70) -- CursorPrepare --insert @trace_events (eventid) values (71) -- Prepare SQL --insert @trace_events (eventid) values (72) -- Exec Prepared SQL --insert @trace_events (eventid) values (73) -- Unprepare SQL insert @trace_events (eventid) values (74) -- CursorExecute --insert @trace_events (eventid) values (75) -- CursorRecompile --insert @trace_events (eventid) values (76) -- CursorImplicitConversion --insert @trace_events (eventid) values (77) -- CursorUnprepare --insert @trace_events (eventid) values (78) -- CursorClose insert @trace_events (eventid) values (79) -- Missing Column Statistics insert @trace_events (eventid) values (80) -- Missing Join Predicate --insert @trace_events (eventid) values (81) -- Server Memory Change insert @trace_events (eventid) values (82) -- User Configurable 0 insert @trace_events (eventid) values (83) -- User Configurable 1 insert @trace_events (eventid) values (84) -- User Configurable 2 insert @trace_events (eventid) values (85) -- User Configurable 3 insert @trace_events (eventid) values (86) -- User Configurable 4 insert @trace_events (eventid) values (87) -- User Configurable 5 insert @trace_events (eventid) values (88) -- User Configurable 6 insert @trace_events (eventid) values (89) -- User Configurable 7 insert @trace_events (eventid) values (90) -- User Configurable 8 insert @trace_events (eventid) values (91) -- User Configurable 9 insert @trace_events (eventid) values (92) -- Data File Auto Grow insert @trace_events (eventid) values (93) -- Log File Auto Grow insert @trace_events (eventid) values (94) -- Data File Auto Shrink insert @trace_events (eventid) values (95) -- Log File Auto Shrink --insert @trace_events (eventid) values (96) -- Show Plan Text --insert @trace_events (eventid) values (97) -- Show Plan ALL --insert @trace_events (eventid) values (98) -- Show Plan Statistics --insert @trace_events (eventid) values (99) -- Reserved --insert @trace_events (eventid) values (100) -- RPC Output Parameter --insert @trace_events (eventid) values (101) -- Reserved --insert @trace_events (eventid) values (102) -- Audit Statement GDR --insert @trace_events (eventid) values (103) -- Audit Object GDR --insert @trace_events (eventid) values (104) -- Audit Add/Drop Login --insert @trace_events (eventid) values (105) -- Audit Login GDR --insert @trace_events (eventid) values (106) -- Audit Login Change Property --insert @trace_events (eventid) values (107) -- Audit Login Change Password --insert @trace_events (eventid) values (108) -- Audit Add Login to Server Role --insert @trace_events (eventid) values (109) -- Audit Add DB User --insert @trace_events (eventid) values (110) -- Audit Add Member to DB --insert @trace_events (eventid) values (111) -- Audit Add/Drop Role --insert @trace_events (eventid) values (112) -- App Role Pass Change --insert @trace_events (eventid) values (113) -- Audit Statement Permission --insert @trace_events (eventid) values (114) -- Audit Object Permission --insert @trace_events (eventid) values (115) -- Audit Backup/Restore --insert @trace_events (eventid) values (116) -- Audit DBCC --insert @trace_events (eventid) values (117) -- Audit Change Audit --insert @trace_events (eventid) values (118) --Audit Object Derived Permission -- Specify which data columns to include in trace --  (uncomment insert statements for desired columns) insert @trace_columns (columnid) values (1) --TextData insert @trace_columns (columnid) values (2) --BinaryData insert @trace_columns (columnid) values (3) --DatabaseID insert @trace_columns (columnid) values (4) --TransactionID insert @trace_columns (columnid) values (5) --Reserved insert @trace_columns (columnid) values (6) --NTUserName insert @trace_columns (columnid) values (7) --NTDomainName insert @trace_columns (columnid) values (8) --ClientHostName insert @trace_columns (columnid) values (9) --ClientProcessID insert @trace_columns (columnid) values (10) --ApplicationName insert @trace_columns (columnid) values (11) --SQLSecurityLoginName insert @trace_columns (columnid) values (12) --SPID insert @trace_columns (columnid) values (13) --Duration insert @trace_columns (columnid) values (14) --StartTime insert @trace_columns (columnid) values (15) --EndTime insert @trace_columns (columnid) values (16) --Reads insert @trace_columns (columnid) values (17) --Writes insert @trace_columns (columnid) values (18) --CPU insert @trace_columns (columnid) values (19) --Permissions insert @trace_columns (columnid) values (20) --Severity insert @trace_columns (columnid) values (21) --EventSubClass insert @trace_columns (columnid) values (22) --ObjectID insert @trace_columns (columnid) values (23) --Success insert @trace_columns (columnid) values (24) --IndexID insert @trace_columns (columnid) values (25) --IntegerData insert @trace_columns (columnid) values (26) --ServerName insert @trace_columns (columnid) values (27) --EventClass insert @trace_columns (columnid) values (28) --ObjectType insert @trace_columns (columnid) values (29) --NestLevel insert @trace_columns (columnid) values (30) --State insert @trace_columns (columnid) values (31) --Error insert @trace_columns (columnid) values (32) --Mode insert @trace_columns (columnid) values (33) --Handle insert @trace_columns (columnid) values (34) --ObjectName insert @trace_columns (columnid) values (35) --DatabaseName insert @trace_columns (columnid) values (36) --Filename insert @trace_columns (columnid) values (37) --ObjectOwner insert @trace_columns (columnid) values (38) --TargetRoleName insert @trace_columns (columnid) values (39) --TargetUserName insert @trace_columns (columnid) values (40) --DatabaseUserName insert @trace_columns (columnid) values (41) --LoginSID insert @trace_columns (columnid) values (42) --TargetLoginName insert @trace_columns (columnid) values (43) --TargetLoginSID insert @trace_columns (columnid) values (44) --ColumnPermissionsSet -- Build full path of tracefile -- The format of the filename is sqltrace_YYYMMDD_hhmm.trc -- The .trc extension will be appended to the filename automatically IF RIGHT (@strTraceDirectory, 1) <> '\'   SELECT @strTraceDirectory = @strTraceDirectory + '\' SELECT @strTraceFile = @strTraceDirectory + @tracefile_basename   + '_' +  CONVERT (varchar, GETDATE(), 112)   + '_' +  REPLICATE ('0', 2-LEN (DATEPART (hh, GETDATE())))   + CAST (DATEPART (hh, GETDATE()) AS varchar)   + REPLICATE ('0', 2-LEN (DATEPART (mi, GETDATE())))   + CAST (DATEPART (mi, GETDATE()) AS varchar) -- Create the trace definition to get the TraceID -- option values --    2 - Rollover Trace File when maxfilesize reached --    4 - Shutdown on error --    6 - options 2 & 4 --    8 - Produce black box trace exec @rc = sp_trace_create @traceid = @TraceID output,                            @options = 2,                            @tracefile = @strTraceFile,                            @maxfilesize = @maxfilesize,                            @stoptime = @stoptime if (@rc != 0) goto error -- Set the events and columns to be included in the trace -- sp_trace_setevent must be called for each column for --  each event, hence the nested cursors declare event_cursor cursor for select eventid from @trace_events declare column_cursor cursor for select columnid from @trace_columns open event_cursor declare @event_id int,         @column_id int,         @on bit select @on = 1 fetch event_cursor into @event_id while @@fetch_status = 0 -- for each event begin     open column_cursor     fetch column_cursor into @column_id     while @@fetch_status = 0  -- for each data column     begin         exec sp_trace_setevent @traceid = @TraceID,                                @eventid = @event_id,                                @columnid = @column_id,                                @on = @on         fetch column_cursor into @column_id     end     close column_cursor     fetch event_cursor into @event_id end close event_cursor deallocate column_cursor deallocate event_cursor -- specify the Filters on the trace -- Add a call to sp_trace_setfilter for each filter to apply -- Logical operator is either 0 (AND) or 1 (OR) -- Value Comparison operators are as follows: --    0 = (Equal) --    1 <> (Not Equal) --    2 > (Greater Than) --    3 < (Less Than) --    4 >= (Greater Than Or Equal) --    5 <= (Less Than Or Equal) --    6 LIKE --    7 NOT LIKE exec @rc = sp_trace_setfilter @traceid = @TraceID,                               @columnid = 1,                               @logical_operator = 0,                               @comparison_operator = 7,                               @value = N'%xp_trace%' if (@rc != 0) goto error exec @rc = sp_trace_setfilter @traceid = @TraceID,                               @columnid = 1,                               @logical_operator = 0,                               @comparison_operator = 7,                               @value = N'%sp_trace%' if (@rc != 0) goto error exec @rc = sp_trace_setfilter @traceid = @TraceID,                               @columnid = 10,                               @logical_operator = 0,                               @comparison_operator = 7,                               @value = N'%Profiler%' if (@rc != 0) goto error exec @rc = sp_trace_setfilter @traceid = @TraceID,                               @columnid = 10,                               @logical_operator = 0,                               @comparison_operator = 7,                               @value = N'%SQLEM%' if (@rc != 0) goto error -- exec @rc = sp_trace_setfilter @traceid = @TraceID, --                               @columnid = 10, --                               @logical_operator = 0, --                               @comparison_operator = 7, --                               @value = N'%Query Analyzer%' -- if (@rc != 0) goto error exec @rc = sp_trace_setfilter @traceid = @TraceID,                               @columnid = 10,                               @logical_operator = 0,                               @comparison_operator = 7,                               @value = N'%SQLAgent%' if (@rc != 0) goto error -- exec sp_trace_setfilter @traceid = @TraceID, --                         @columnid = 3, -- Database ID --                         @logical_operator = 0, --                         @comparison_operator = 0, --                         @value = 8 -- Set the trace status to start the trace exec @rc = sp_trace_setstatus @traceid = @TraceID,                               @status = 1 if (@rc != 0) goto error -- Display trace id for future references Print 'TraceID for current trace = ' + ltrim(str(@TraceID)) goto finish error: Print 'Trace setup exited with return code = ' + ltrim(str(@rc)) finish: go 

TIP

If you want to always capture certain trace events when SQL Server is running, such as auditing-type events, you can create a stored procedure that uses the sp_trace stored procedures to create a trace and specify the events to be captured. You can use the code in Listing 7.2 as a basis to create the stored procedure. Then mark the procedure as a startup procedure using the sp_procoption procedure to set the autostart option. The trace will automatically start when SQL Server is started and will continue running in the background. Just be aware that although server-side traces are less intrusive than using the SQL Profiler client, some overhead is necessary to run a trace. Try to limit the number of events captured to minimize the overhead as much as possible.

Monitoring Running Traces

SQL Server 2000 provides some additional built-in user-defined functions to get information about currently running traces. Like the fn_trace_gettable function discussed previously, these functions return the information as a table result. The available functions are as follows:

  • fn_trace_getinfo( trace_id ) This function is passed a traceid and it returns information about the specified trace. If passed the value of default , it returns information about all existing traces. An example of the output from this function is shown in Listing 7.3.

  • fn_trace_geteventinfo( trace_id ) This function returns a list of the events and data columns being captured for the specified trace. Only the event and column ID values are returned. You can use the information provided in Tables 7.2 and 7.3 to map the IDs to the more meaningful event names and column names .

  • fn_trace_getfilterinfo( trace_id ) This function returns information about the filters being applied to the specified trace. Again, the column ID and logical and comparison operator values are returned as integer IDs that you'll need to decipher. See Table 7.4 for a listing of the column operator values.

Listing 7.3 Examples of Using the Built-In User-Defined Functions for Monitoring Traces
 set nocount on go SELECT * FROM ::fn_trace_getinfo(default) go traceid     property    value                             ----------- ----------- ------------------------------------ 1           1           2 1           2           c:\temp\sampletrace_20020826_0459 1           3           100 1           4           NULL 1           5           1 select * from ::fn_Trace_getfilterinfo(1) go columnid    logical_operator comparison_operator value  ----------- ---------------- ------------------- ------------- 1           0                7                   %xp_trace% 1           0                7                   %sp_trace% 10          0                7                   %Profiler% 10          0                7                   %SQLEM% 10          0                7                    %SQLAgent% 

The property values returned by fn_trace_getinfo are again specified as integer IDs. Table 7.5 describes these property IDs.

Table 7.5. Description of Trace Property ID Values
Property ID Description
1 Trace options specified in sp_trace_create
2 Trace filename
3 Maximum size of trace file in MB
4 Date and time the trace will be stopped
5 Current trace status

Stopping Server-Side Traces

Before you can access the trace file generated by a server-side trace, you must first stop the trace and then close and delete the trace from SQL Server. If you specified a stop time when you started the trace, it will automatically stop and close when the stop time is reached. For example, in the SQL script in Listing 7.2, if you wanted the trace to run for 15 minutes instead of indefinitely, set the value for the stoptime variable at the beginning of the script using a command similar to the following:

 set @stoptime = dateadd(minute, 15, getdate()) 

To otherwise stop a running server-side trace, use the sp_trace_setstatus stored procedure and pass it the trace ID and a status of 0. Stopping a trace only stops gathering trace information and does not delete the trace definition from SQL Server. Essentially, it pauses the trace. You can restart the trace by passing sp_trace_setstatus a status value of 1.

Once you've stopped the trace, you can close the trace and delete its definition from SQL Server by passing sp_trace_setstatus the ID of the trace you want to stop and a status of 2. Once you've closed the trace, you must redefine it before you can restart it.

If you don't know the ID of the trace you want to stop, use the fn_trace_getinfo function to return a list of all running traces and select the appropriate trace ID. The following shows an example of stopping and closing a trace with a trace ID of 1:

 -- Set the trace status to stop  exec sp_trace_setstatus 1, 0 go -- Close and Delete the trace exec sp_trace_setstatus 1, 2 go 

If you want to stop and close multiple traces, you must call sp_trace_setstatus twice for each trace. Listing 7.4 provides an example of a system stored procedure that you could create in SQL Server to stop a specific trace or automatically stop all currently running traces.

Listing 7.4 Sample System Stored Procedure to Stop Profiler Traces
 use master go if object_id ('sp_OCI_stop_trace') is not null     drop proc sp_OCI_stop_trace go create proc sp_OCI_stop_trace @TraceID int = null as if @TraceID is not null begin     -- Set the trace status to stop     exec sp_trace_setstatus @TraceID, 0     -- close and delete the trace     exec sp_trace_setstatus @TraceID, 2 end else  -- get a list of all current traces begin     declare c1 cursor for     SELECT distinct traceid FROM :: fn_trace_getinfo(default)     open c1     fetch c1 into @TraceID     while @@fetch_status = 0     begin         -- Set the trace status to stop         exec sp_trace_setstatus @TraceID, 0         -- close and delete the trace         exec sp_trace_setstatus @TraceID, 2         fetch c1 into @TraceID     end     close c1     deallocate c1 end 


Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net