Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've read before that its possible to create a SQL profiler trace that records directly to a table without neeing to use the SQL profiler - apparently this is better than having the SQL profiler itself running as the SQL profiler captures all events, and then filters entries in the profiler itself to display only the ones specified in the current filter. The direct trace only collects events specified in the trace itself, resulting in less of an impact on the server itself.

I want to set up a trace that records all queries taking longer than 1 second to execute, but now I cant seem to find that article again.

share|improve this question
up vote 6 down vote accepted

I was shown a trick a while back to do just that. You can easily get the trace definition by creating your trace with SQL Profiler, starting it, then immediately stopping it. At that point, Profiler will allow you to export the SQL statements to create the same thing server-side. You'll still need to modify a few data points like mrdenny noted, but it takes most of the work out of creating the server-side trace. The location of that command on SQL2005+ is File > Export > Script Trace Definition; on SQL2000 it is at File > Script Trace.

share|improve this answer
Thanks - much easier than creating the script myself. – Justin Oct 8 '09 at 10:12

Yes, its recommended that 'server side' traces are used in a production environment instead of using SQL Profiler to do 'client side' traces, not because profiler captures all event but because of the memory and network overhead required in running Profiler.

As already suggested, the best way to create a server side trace is to setup your filtering in SQL Server Profiler, then export the trace to a script.

There's a great step-by-step article on sqlserverpedia that explains how to do it, is this the article you were after?

share|improve this answer
Thats not the one, but thats also a brilliant article. – Justin Oct 8 '09 at 10:13

Running SQL Profiler (with profiler running on a client) and running a trace from T/SQL will do the exact same thing. The filtering is all done on the server.

This can be seen by setting up a SQL Profiler trace to monitor SQL PRofiler, then start that trace. Then setup a second trace to handle what you want, then start the second trace. In the first trace you'll see all the T/SQL commands to create the trace and setup the filtering.

You'll need to tweak the code to use T/SQL variables to handle the fact that the TraceId could be different from the one used when you setup the sample trace.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.