Posted by Plamen Ratchev on 02/21/07 15:30

Here are the steps that can help you accomplish what you need:

1. Define your SQL Profiler Trace and script it. A good idea to place
restrictive filters to minimize the load on the production system. Make sure
the trace target is a file, not a table as it will have significant
performance impact. To script the trace use:
- SQL Server 2000 -> File -> Script Trace
- SQL Server 2005 -> File -> Export -> Script Trace Definition

2. Modify the trace script and wrap it in a stored procedure. Add a couple
parameters to pass the target trace file, source database. etc. One of the
first statements in the generated trace script is calling sp_trace_create,
where you need to replace the target trace file as a parameter. And you can
add a call to sp_trace_setfilter (for example, EXEC sp_trace_setfilter
@TraceId, 3, 0, 0, @DbId) to set a filter for a specific database based on
database id that you can pass to the SP (or do not set that filter if you
want to trace for all databases). Also, the database can be one of the
preset filters in step 1 above.

3. Inside the stored procedure you can record the trace id to a table, so
later on if needed you can use the SP sp_trace_setstatus to stop the trace.

4. Create a job to schedule the stored procedure. In the schedule specify to
run automatically when SQL Server Agent starts.

BTW, this is the recommended way to run traces on production systems. If you
run a trace via the GUI you are actually running two traces: one that send
the trace to the target file, and another that sends the trace data to the
client running Profiler.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]