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

Komal Sahu 311 Reputation points
2020-09-20T15:06:04.42+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,495 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
999 questions
No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 67,721 Reputation points Microsoft MVP
    2020-09-20T15:23:17.66+00:00

    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.

    No comments

9 additional answers

Sort by: Most helpful
  1. Darren Gosbell 2,361 Reputation points
    2020-09-29T06:46:34.827+00:00

    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

    No comments

  2. Darren Gosbell 2,361 Reputation points
    2020-10-09T08:03:41.237+00:00

    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.

    No comments

  3. Lukas Yu -MSFT 5,796 Reputation points
    2020-09-21T03:36:57.157+00:00

    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.

    No comments

  4. Komal Sahu 311 Reputation points
    2020-09-21T06:35:33.917+00:00

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

    No comments