Run SQL Trace in background

Rather useful this if you need to monitor a trace for a period of time and can not leave the session logged in. Found how to do it here.

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

Easy !