469,125 Members | 1,276 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

How to run profiler from cmd


We have a requirement to run profiler continuously to find out the
misbehaving applications/SQL in the dataserver. And we are not
interested in running it from an individuals local machine. Also we
want the profiler to start automatically whenever there are SQL Server/
Machine restarts.

Is there a way to invoke the profiler instance from cmd prompt or is
there a way to script the profiler?

Thanks in advance,

Feb 21 '07 #1
1 5725
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.


Plamen Ratchev
Feb 21 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeff Mair | last post: by
8 posts views Thread by patrickshroads | last post: by
9 posts views Thread by Franco Gustavo | last post: by
3 posts views Thread by Maansi Gupta | last post: by
8 posts views Thread by Bryan | last post: by
1 post views Thread by ofirmgr | last post: by
3 posts views Thread by Sam Samson | last post: by
2 posts views Thread by Paul Ritchie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.