In SQL Server 2000, system stored procedures are used to implement server-side tracing, but there is no way to programmatically trace directly to a table. However, you can easily import the trace file into a table by using the
function. SQL Server tracing is implemented in two ways:
Server-side implementation performed by system stored procedures
Client-side implementation performed by SQL Profiler
In SQL Server 7.0, you can use extended stored procedures programmatically and trace directly to a table.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
INF: How to Trace in SQL Server by Using Extended Stored Procedures
For detailed information about the mapping between these extended stored procedures and the new system stored procedures, see the
"Creating and Managing Traces and Templates"
topic in SQL Server Books Online.
If you use SQL Profiler to trace SQL Server data, SQL Profiler is not guaranteed to capture all the events that are generated, especially in an environment with high CPU utilization. Microsoft recommends that you use system stored procedures to trace SQL Server data in those situations.
Trace SQL Server Data by Using Stored Procedures
DECLARE @RC int, @TraceID int, @on BIT
EXEC @rc = sp_trace_create @TraceID output, 0, N'C:\test'
-- Select the return code to see if the trace creation was successful.
SELECT RC = @RC, TraceID = @TraceID
-- Set the events and data columns you need to capture.
-- Here, you are just capturing one event. Add additional events as necessary.
SELECT @on = 1
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 2, @on
EXEC sp_trace_setevent @TraceID, 10, 3, @on
EXEC sp_trace_setevent @TraceID, 10, 6, @on
EXEC sp_trace_setevent @TraceID, 10, 7, @on
EXEC sp_trace_setevent @TraceID, 10, 8, @on
EXEC sp_trace_setevent @TraceID, 10, 9, @on
EXEC sp_trace_setevent @TraceID, 10, 10, @on
-- Set any filter.
EXEC sp_trace_setfilter 1, 10, 0, 6, N'%Profiler%'
-- Start Trace (status 1 = start)
EXEC @RC = sp_trace_setstatus @TraceID, 1
Create a SQL Server Script to Use System Stored Procedures
Although you can use the previous code sample, it is easier to generate a SQL script that uses system stored procedures. To do so, follow these steps:
Start SQL Profiler, connect to an instance of SQL Server 2000, and then follow these steps:
Create a new trace. On the
menu, point to
New, and then click
dialog box, click the
Specify the trace name, the computer running SQL Server that you want to trace, and then click to select the Save to file
Specify a file name, and then click to clear the Enable file rollover
tab, and then add all the required events.
tab, and then add all the required data columns.
tab, and then specify the filtering criteria.
The trace starts to capture the events.
Stop the trace. To do so, on the
menu, point to
Script Trace, and then click
For SQL Server 2000.
Save the file as mytrace.sql.
Open the Mytrace.sql file in Notepad, and then make the following changes:
Change the third parameter (@tracefile) of the
procedure call to specify where to save the trace file.
Change the optional fourth parameter (@maxfilesize) of the
procedure call to specify the maximum size of the trace file.
Start SQL Query Analyzer, connect to an instance of SQL Server 2000, and then run the Mytrace.sql file.
View the return code.
If the return code is not zero, see the
topic in SQL Server Books Online. If the return code is zero, the trace started successfully, and SQL Server is traced until you stop the trace.
To stop the trace, follow these steps:
Start SQL Query Analyzer on the instance of SQL Server 2000 that you are tracing.
Run the following statement, and then identify the trace that you want to stop from the list of trace IDs:
Select * from ::fn_trace_getinfo(default)
After you identify the TraceID, run the following statements to stop and delete the trace:
-- First stop the trace
EXEC sp_trace_setstatus TraceId, 0
-- Close and then delete its definition from SQL Server
EXEC sp_trace_setstatus TraceId, 2
Use the following statement to programmatically load the trace file to a database table:
SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\my_trace.trc', default)
You can use the
function to load the trace file to a table if you have disabled the rollover option or load multiple trace files if you have enabled the rollover option.
Note If you use the rollover trace files that are generated by the GUI Profiler, the
function cannot load the rollover files to a table. The fn_trace_gettable
function can only read the rollover files that are generated by system stored procedures and load those files to a table.