Microsoft KB Archive/258990

= INF: How to Trace in SQL Server by Using Extended Stored Procedures =

Article ID: 258990

Article Last Modified on 7/27/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



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:   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  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:  In SQL Server Enterprise Manager, expand the Management folder. Expand the SQL Server Agent icon. Right-click Jobs, and then click New Job on the shortcut menu.

In the General tab:

 Specify a name for the job.</li> Leave the category at the default of Uncategorized.</li> Specify the owner of the job that has permissions to execute the extended stored procedures for tracing.</li> Give a brief description of what you want.</li></ol>

In the Steps tab, click on New Step, and choose the following in the General tab:

 Specify the step name.</li> Choose the Type as Operating System Command(CmdExec).</li> Leave the Process Exit code at the default value of 0.</li> Enter the following for the Command: osql -E -S server_name -i &quot;C:\temp\xp_scheduled_trace.sql&quot;. </li> Change the &quot;server_name&quot; to the correct SQL Server in the preceding command without the double quotes.</li></ol>

In the Schedules tab, click on the New Schedule button, and do the following:

 Specify a name for the job schedule.</li> 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.</li></ol> </li> 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.</li>  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 </li> To disable the job if you don't need to trace anymore: <ol> <li>In SQL Server Enterprise Manager, expand the Management folder.</li> <li>Expand the SQL Server Agent icon.</li> <li>Click Jobs.</li> <li>Right-click the job you created above and select Disable Job on the shortcut menu.</li></ol> </li></ol>

Additional query words: automate

Keywords: kbinfo kbsqlprog KB258990

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.