INF: How to Trace in SQL Server by Using Extended Stored Procedures (258990)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q258990

SUMMARY

SQL Server Profiler (SQL Trace) is a graphical tool that allows system administrators and database developers to monitor engine events on computers running Microsoft SQL Server. Traces can be created by using SQL Server Profiler, extended stored procedures, or the Create Trace Wizard. This article specifically addresses the programmatic approach of tracing the engine events by using the system-supplied extended stored procedures.

MORE INFORMATION

The use of extended stored procedures lightens the performance impact of tracing on a heavily stressed server because less overhead is incurred compared to the GUI. The approach of using extended stored procedures for tracing allows you to write custom applications that can do the following:
  • Send trace data to the Microsoft Windows NT application log.
  • Autostart a trace when SQL Server starts.
  • Forward trace events from one or more servers to another SQL Server.
  • Configure queues and consumers.
This article gives you steps to create traces using extended stored procedures and does not address the preceding possibilities. You can get more information on those in SQL Server 7.0 Books Online.

The following definitions help you better understand the underlying architecture that is exposed by the extended stored procedures:

Producer

Producers are those that generate the events to be monitored. An example would be the SQL Server lock manager, which generates lock events.

Filter

Filters are used to restrict the data monitored by the trace.

Queue

A queue collects events and queues them for asynchronous processing by consumers.

Consumers

Consumers are those that extract the events from the queue. For example, the file consumer extracts the events from the queue and writes them to a destination file.

Destination

This is the place to send the event data when it is extracted from the queue. Destinations include files, SQL Server tables, other computers running SQL Server, and the Microsoft Windows NT application log.

The general steps required to define your own trace using extended stored procedures are:
  1. Create a new queue by using xp_trace_addnewqueue.
  2. Specify the events to capture by using xp_trace_seteventclassrequired.
  3. Specify any event filters by using xp_trace_setXXXXXfilter.
  4. Specify the destination for the captured event data by using xp_trace_setqueuedestination.
NOTE: The events and data columns captured in the following script are for illustration purposes only and they should be modified according to your specific needs.

The actual steps to start tracing are as follows:
  1. Save the following script into a file named xp_scheduled_trace.sql in the C:\temp directory:
    USE master
GO

-- Declare variables
DECLARE @old_queue_handle int -- Queue handle of currently running trace queue
DECLARE @queue_handle int -- Queue handle for new running trace queue
DECLARE @column_value int -- Data column bitmask
DECLARE @trace_name varchar(40) -- Name of the trace definition
DECLARE @date datetime -- Date component of file name
DECLARE @file_name varchar(80) -- Trace file name

-- Set up trace definition
SELECT @trace_name = 'auto_trace'

-- Set up the trace file name
-- This script appends the datetime to the file name each time it is
-- run to create a new, unique file name.
SELECT @file_name = 'c:\temp\auto_trace_'

-- Stop the trace queue if running
IF EXISTS( SELECT name FROM sysobjects WHERE name = 'trace_queue_table' )
BEGIN
  IF EXISTS( SELECT * FROM trace_queue_table WHERE queue_name = @trace_name )
  BEGIN
    SELECT @old_queue_handle = queue_handle
      FROM trace_queue_table
     WHERE queue_name = @trace_name
    EXEC xp_trace_destroyqueue @old_queue_handle
    PRINT'Deleted trace queue ' + CONVERT( VARCHAR(20),@old_queue_handle )
  END
END

-- Set the column mask for the data columns to capture.
-- Text data | Binary data | Database ID | Transaction ID | Connection ID|
-- Username | Domain name | Host name | Host process ID | Application name|
-- SQL username | SPID | Duration | Start time | End time | Reads | Writes|
-- CPU usage | Severity | Event Subclass | Object ID | Index ID |
-- Integer data | Server name | Event Class 
SET @column_value =  1|2|4|8|16|32|64|128|256|512|1024|2048|4096|8192|16384|32768|
65536|131072|524288|1048576|2097152|8388608|16777216|33554432|67108864

-- Create the trace queue
EXEC xp_trace_addnewqueue 11000, 10000, 95, 90, @column_value, @queue_handle output

-- Specify the event classes to trace
-- To list all the event classes, execute the procedure xp_trace_geteventnames using Query Analyzer 
-- or look up for that procedure in SQL Server BOL and add to the following list as needed
EXEC xp_trace_seteventclassrequired @queue_handle, 11 ,1 -- RPC:Starting
EXEC xp_trace_seteventclassrequired @queue_handle, 13 ,1 -- SQL:BatchStarting
EXEC xp_trace_seteventclassrequired @queue_handle, 14 ,1 -- Connect
EXEC xp_trace_seteventclassrequired @queue_handle, 15 ,1 -- Disconnect
EXEC xp_trace_seteventclassrequired @queue_handle, 16 ,1 -- Attention
EXEC xp_trace_seteventclassrequired @queue_handle, 17 ,1 -- Existing Connection
EXEC xp_trace_seteventclassrequired @queue_handle, 40 ,1 -- SQL:StmtStarting
EXEC xp_trace_seteventclassrequired @queue_handle, 43 ,1 -- SP:Completed
EXEC xp_trace_seteventclassrequired @queue_handle, 45 ,1 -- SP:StmtCompleted

-- Set filters (don't trace the trace activity itself)
-- You can add other filters like application name etc. by looking at the xp_trace_set% procedures in SQL Server BOL
EXEC xp_trace_settextfilter @queue_handle, NULL, 'EXEC xp_trace%;SET ANSI%'

-- Configure the queue to write to a file
SELECT @date = getdate()
SELECT @file_name = @file_name + 
CONVERT( varchar(4), datepart(yy,@date)) +
CONVERT( varchar(2), datepart(mm,@date)) + 
CONVERT( varchar(2), datepart(dd, @date)) + 
CONVERT( varchar(2), datepart(hh, @date)) +
CONVERT( varchar(2), datepart(mi, @date)) +
CONVERT( varchar(2), datepart(ss, @date)) +'.trc'

PRINT 'The trace file name is : ' + @file_name
EXEC xp_trace_setqueuedestination @queue_handle, 2, 1, NULL, @file_name

-- Start the consumer that actually writes to the file
EXEC xp_trace_startconsumer @queue_handle

-- Record the trace queue handle for subsequent jobs.
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'trace_queue_table' )
BEGIN
  CREATE TABLE trace_queue_table ( queue_handle int, queue_name varchar(20) )
  INSERT INTO trace_queue_table VALUES( @queue_handle, @trace_name )
  PRINT 'Created table and inserted queue handle ' + CONVERT( varchar(20), @queue_handle )
END
ELSE
BEGIN
  IF EXISTS ( SELECT * FROM trace_queue_table WHERE queue_name = @trace_name )
  BEGIN
    UPDATE trace_queue_table SET queue_handle = @queue_handle WHERE queue_name = @trace_name
    PRINT 'Updated table with new queue handle ' + CONVERT( varchar(20), @queue_handle )
  END
  ELSE
  BEGIN
    INSERT INTO trace_queue_table VALUES( @queue_handle, @trace_name )
    PRINT 'Inserted row into table with new queue handle ' + CONVERT(  varchar(20), @queue_handle )
  END

END
  2. Schedule a task to start every hour or so depending on your preference so that it generates a new trace file every time the task executes. You can accomplish this by following these steps:
    1. In SQL Server Enterprise Manager, expand the Management folder.
    2. Expand the SQL Server Agent icon.
    3. Right-click Jobs, and then click New Job on the shortcut menu.
    In the General tab:

    1. Specify a name for the job.
    2. Leave the category at the default of Uncategorized.
    3. Specify the owner of the job that has permissions to execute the extended stored procedures for tracing.
    4. Give a brief description of what you want.
    In the Steps tab, click on New Step, and choose the following in the General tab:

    1. Specify the step name.
    2. Choose the Type as Operating System Command(CmdExec).
    3. Leave the Process Exit code at the default value of 0.
    4. Enter the following for the Command: osql -E -S server_name -i "C:\temp\xp_scheduled_trace.sql".
    5. Change the "server_name" to the correct SQL Server in the preceding command without the double quotes.
    In the Schedules tab, click on the New Schedule button, and do the following:

    1. Specify a name for the job schedule.
    2. Change the schedule according to your preference, for instance, every hour, so that a new trace file is created every hour. You can change the schedule to meet your specific needs.
  3. Start the job to start tracing on SQL Server by right-clicking on the Job and choosing Start Job. This starts tracing and small manageable trace files are generated in the C:\temp directory.
  4. To stop tracing, execute the following code in Query Analyzer:
    -- Get a list of all the trace queues and their corresponding queue handles
-- if entered before adding new queues and choose a queue handle to destroy
SELECT * FROM trace_queue_table

-- If a queue is not entered into the trace table, you can get a list of queues running on the Server by 
-- executing the following extended stored procedure and choose a queue handle to destroy
EXEC xp_trace_enumqueuehandles

-- Destroy the queue
EXEC xp_trace_destroyqueue <queue_handle_from_above_step>
For Example, EXEC xp_trace_destroyqueue 9
  5. To disable the job if you don't need to trace anymore:
    1. In SQL Server Enterprise Manager, expand the Management folder.
    2. Expand the SQL Server Agent icon.
    3. Click Jobs.
    4. Right-click the job you created above and select Disable Job on the shortcut menu.
Modification Type:MajorLast Reviewed:7/27/2001
Keywords:kbDSupport kbinfo kbSQLProg KB258990
©2002 Microsoft Corporation. All rights reserved.