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

I also check if xmla can be executed by SSIS but didnt find any related task in SSIS which can execute xmla on SSAS server.

There is a task in SSIS it's a control flow task called Analysis Services Execute DDL Task

Or you can put XMLA directly in a SQL Agent job using a SQL Analysis Services Command step

Answer 3

I am Admin of the machine and running ssms/ssis as administrator.

It does not matter what rights you have on the machine - the trace file gets written out by the SSAS service. So it is the SSAS service account that needs to have permission to the folder where you are writing this file. I would suggest creating a folder for these traces, not writing directly to the root of C: drive, then you can give the SSAS service account rights to just that folder.

Answer 4

Hi,
You could look into this bolg, which shows a way to scheduling such job. Scheduling a SQL Server Profiler Trace
You could define the starttime and stoptime in the code, also change the object correspondingly to fit you requirement.

Regards,
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Many thanks for your comments,
Let me explore and try these methods..