How to schedule SQL Profiler traces for auto start and stop in a particular time

Question

Hi ALL,

In any way, Is it possible if we can schedule SQL profiler for future timing?
That means, for example, can we schedule SQL profiler (NOW) to auto start/run the traces tomorrow at 5 AM -capture the traces - and then auto-stop tomorrow at 7:45 AM .

Further we will be able to analyze the trace for performance tuning or troubleshooting.
Specifically looking to capture traces on 'SSAS server'.

Any idea?

Answer 1

I don't know anything about SSAS, so if there are differences to the Database Engine, I can't speak to that.

You would never schedule a trace with Profiler. This is because running Profiler against a production system can have sever consequences.

However, what makes sense is to use Profiler to set up a trace with the events and columns you want, and possibly also testing the trace for a short instant. Then you can script the trace on an SQL file and run it server-side. You do that from File->Export in the menu. Modify the script to specify the location of the trace file. You can view the trace file in Profiler, or read into a table with sys.fn_trace_gettable.

When you set up the trace, you can specify a stop time. You do that in the lower-most pane of the start page for the Trace definition. This stop time is included in the script you export.

As for the starting time there are two options. One is to put the trace definition in a Agent job which you schedule to run when you want the trace to start. This is arguably the better option as the trace does not even exist and take any load.

The other option, which is the one I have used myself, is to set a filter on the StartTime column for the desired starting time. You start the trace directly, but it will not capture anything before you configured start time.

Answer 2

do you handle scheduler to start this job daily ? and how you will do it even trace id be the same?