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 Server?stored 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

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

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.

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