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.
12,653 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.
1,243 questions
0 comments
Accepted answer
  1. Erland Sommarskog 100.8K Reputation points 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.

    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-09-25T05:36:55.9+00:00

    Hi,
    Any news from this case ? Have you resolve this issue ?

    0 comments
  2. Komal Sahu 311 Reputation points
    2020-09-29T05:57:07.797+00:00

    Hi All,

    Thanks very much for your comments..
    I tried to follow the steps given in the mentioned article and also mentioned by ErlandSommarskog.
    It seems these steps are more to capture the traces on SQL Server Relational DB. Still I tried same steps to check if I can achieve that in SSAS DB.
    When exported trace file, it got saves as xmla. below is a glimpse of it.

    28987-image.png

    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.
    If anyone has some more guidance/suggestion how should I go further?
    Many Thanks,

  3. Komal Sahu 311 Reputation points
    2020-10-09T07:21:22.06+00:00

    Hi All,
    Thanks for further suggestion on this.
    I followed this article suggestion by @Erland Sommarskog and it is very straight forward to achieve the goal.
    But I stuck in a error which I got while executing xmla script as below:

    <Error ErrorCode="3238133761" Description="File system error: The following error occurred while opening the file 'C:\Test Schedule XMLA_091020201.trc': Access is denied. ." Source="Microsoft SQL Server 2014 Analysis Services" HelpFile="" />

    31232-image.png

    Same error I got when I executed the script in 'Analysis Services Execute DDL Task' using SSIS suggested by @Darren Gosbell .
    I am Admin of the machine and running ssms/ssis as administrator.
    Can I get any hint on this to overcome this error please?

    0 comments
  4. Komal Sahu 311 Reputation points
    2020-10-15T14:32:59.453+00:00

    Hello All Thank you very much for your input..

    I was able to resolve this as suggested by @Erland Sommarskog using this url. It is quite straight forward.
    Thanks @Darren Gosbell as well for your suggestion for SSIS.

    Many Thanks Again,
    Komal

    0 comments