Trace me! - Setting up a trace by TSQL / SP

With thanks to Microsoft

Create a SQL Server Script to Use System Stored ProceduresAlthough 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.

You might also consider setting a Profiler trace to start automatically when SQL Server starts.

You can do this with thexp_trace_setqueueautostart extended stored procedure.

xp_trace_setqueueautostart { 'queue_name', autostart_value} 

where

• queue_name is the name of the trace you want to have start automatically.
• autostart_value is 1 to indicate autostart, 0 to turn it off.

For example, if you configure a trace named myaudittrace with the Profiler utility, you can run

Exec xp_trace_setqueueautostart 'myaudittrace',1

And the next time SQL Server is started, the trace myaudittrace would start.