Posts Tagged ‘Profiler’

SSAS 2005 – Server side tracing starter kit

April 7, 2008 4 comments

Analysis services 2005 (SSAS) added the ability to trace server side events and i have used this feature a number of times. To date i had always used the profiler gui to do the SSAS tracing but today i found myself needing to initiate and manage a trace with scripts.

The good news is that it can be done! It did take a while to piece together how to do it though and i found some of the information quite a challenge to find so i am sharing with you the results and have attached a zip file with the necessary scripts.

So, what did i want to achieve?

  • A script that would create a trace on the server and log to a specified directory similar to the way you can with a SQL Server trace.
  • A script that would list all running traces on an analysis server.
  • A script that would destroy a named running trace, in my case the one i created.

Now, the script that creates the trace is likely to require editing each time to add new events as the script i am attaching only captures command events. The easiest way to define your events is detailed below.

  1. Open SQL Server profiler and define the SSAS trace you require.
  2. Next script the trace by going to “File – Export – Script Trace Definition – For Analysis Services 2005”.
  3. Open the script file and cut & copy the Events & Filter elements into my attached script ensuring you replace the existing Events & Filter elements.

Some people might be wondering why i needed to create the script file if i can script it from profiler? Well, profiler only scripts the events and filters and excludes options such as LogFileName, AutoRestart etc.

So, with the events in place you should now update the LogFileName element with your filename & path and check the LogFileSize element is appropriate. Finally, there is a StopTime element that you can uncomment and set which sets a time for the trace to automatically close but do not forget its the time at the server you are setting not the time where you are.

With all the updating done just run the script to create your own server side SSAS trace. It does not end here though because you will need to stop the trace manually if you have not enabled a StopTime. This is where “Delete Named Trace.xmla” comes in. Simply update the name element and run the script to delete the trace. Unlike SQL Server you do not need to stop and then close the trace. If you are not sure of the name of the trace you can run the script “List all server side traces.xmla” which is also useful for validating that you have removed the trace or that it auto closed. The list traces also gives useful information such as where the traces are outputing their results.

The trace script was amended from an example in Analysis Services Processing Best Practices and i would definitely recommend reading the article. The other scripts i hacked together and are very simple as i am a xmla novice.

I hope you find this information useful, you can locate the scripts here.