Creating ServerTraces using SQL Profiler for background profiling

Follow these steps to create a server trace for either SQL 2000 or SQL 2005.
Server Traces are used for running traces in the background so that SQL server does not have to pump profiling results back to the SQL 2005 Profiler, it can log to a file instead.

Note you can create server trace script files for either the SQL 2000 or SQL 2005 using the SQL 2005 Profiler tool:

• First use Profiler to define the events, columns, and filters needed. Some Events are : SQL:BatchCompleted and RPC:Completed, SP:StmtCompleted. Important columns are : Duration, CPU, Reads and Writes. Some advanced events are SP:Recompile and Scan:Started to check for table and index scans
  
• Click the Run button. Immediately stop the trace
• Click the File menu, expand the Export option, and then expand the Script Trace Definition option. Choose For SQL Server 2005 (or SQL 2000 if creating script for older SQL Server) and select a filename to save the script.
• Once the script has been saved, open it for editing in SQL Server Management Studio.
  The following line of the script must be edited, and a valid path must be specified, including a filename:
  exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere',
  @maxfilesize, NULL. The @maxfilesize is 5MB by default
  
• Run the script. The generated script will also select back a @traceID
• Once you are done use the @traceID to stop and close the trace:
  

EXEC sp_trace_setstatus @traceid=99, @status=0
EXEC sp_trace_setstatus @traceid=99, @status=2

• The fn_trace_gettable function can be used to read the data from the trace file :
  
  SELECT * FROM ::fn_trace_gettable('C:\Traces\myTrace.trc', 999) where 999 is the number of rollover trace files to read