HOW TO: Programmatically Load Trace Files into Tables (270599)

SUMMARY

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 fn_trace_gettable 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: 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.

back to the top

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
GO

				

back to the top

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:

1. Start SQL Profiler, connect to an instance of SQL Server 2000, and then follow these steps:
   1. Create a new trace. On the File menu, point to New, and then click Trace.
   2. In the Trace Properties dialog box, click the General tab.
   3. Specify the trace name, the computer running SQL Server that you want to trace, and then click to select the Save to file check box.
   4. Specify a file name, and then click to clear the Enable file rollover check box.
   5. Click the Events tab, and then add all the required events.
   6. Click the Data Columns tab, and then add all the required data columns.
   7. Click the Filters tab, and then specify the filtering criteria.
2. Click Run.
   
   The trace starts to capture the events.
3. Stop the trace. To do so, on the File menu, point to Script Trace, and then click For SQL Server 2000.
4. Save the file as mytrace.sql.
5. Open the Mytrace.sql file in Notepad, and then make the following changes:
   1. Change the third parameter (@tracefile) of the sp_trace_create procedure call to specify where to save the trace file.
   2. Change the optional fourth parameter (@maxfilesize) of the sp_trace_create procedure call to specify the maximum size of the trace file.
6. Start SQL Query Analyzer, connect to an instance of SQL Server 2000, and then run the Mytrace.sql file.
7. View the return code.
   
   If the return code is not zero, see the "sp_trace_create" 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.
8. To stop the trace, follow these steps:
   1. Start SQL Query Analyzer on the instance of SQL Server 2000 that you are tracing.
   2. 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)
      						

   3. 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
      						

9. Use the following statement to programmatically load the trace file to a database table:

   USE pubs
   GO
   SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\my_trace.trc', default)
   					

   You can use the fn_trace_gettable 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 fn_trace_gettable 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.

back to the top