Recently, a set of AS 2005 Cubes on one of our production servers began to fail repeatedly with fairly meaningless messages. The cubes in question are processed at 20-minute intervals, from data replicated from an Oracle server. As part of the troubleshooting I did, in conjunction with an MS rep, I gathered Trace data - Perfmon Counters, SQL Trace data and AS Trace data. As this server was in 24-hr use, I was limited in how and when I could run Traces - they couldn't be left running permanently (among other restrictions). To stay within the established bounds, I added steps to the cube processing job to turn the Perfmon collections and SQL and AS Traces on and off around the Cube processing step.
As DBA's we are all familiar with Profiler (to one degree or another), and many of us are aware how to script SQL traces to run in a scheduled job. However, I had a hard time finding the information to set up an AS trace in a job. This write-up is going to document how to set up SQL and AS traces in scheduled jobs, the exact events / data fields you want to trace is up to you, I ended up using the default trace templates.
I wrote this with the assumption that you will be running traces around job steps, but you can adapt this for other situations.
I used the default traces for both SQL and AS traces, but removed most of the Events from both the trace definitions in this write-up, just for brevity, you can generate the full definitions from Profiler by following the steps below. I have sections for SQL Server and Analysis Services.
SQL Trace
(1) Create the Trace you want in SQL Profiler, on a SQL connection, setting a Trace Name (I used 'OLAP Error SQL Data'), Save To File with a file name and a file size large enough to hold the data in a single file (I used 32MB), and the Events / Data fields you want to trace. Run the Trace.
(2) Stop the Trace - we don't actually want to collect anything at this point.
(3) Export the trace definition - select "File|Export|Script Trace Defintion|For SQL 2005...". Export the definition to a .SQL file of your choice.
(4) Close Profiler, we're done with it at this point.
In the SQL job with the step you want to trace, you're going to add a couple of steps before the step you want to trace...
(5) Add the following code to a T-SQL job step, named 'Delete SQL Trace File', before the step you want to trace (this step is just to make sure there isn't a file from a previous run, just in case):
| DECLARE @OS_Cmd VARCHAR(1024) SELECT @OS_Cmd = 'DEL /Q /F "C:\Trace Files\OLAP Error SQL Data.trc"' EXECUTE master.dbo.xp_cmdshell @OS_Cmd |
NOTE - you will get an error code 12 from the sp_trace_create call in the next step if the output file already exists.
(6) Add the code from the SQL file you just created to a T-SQL job step, named 'Start SQL Trace', immediately after the 'Delete SQL Trace File' step. Replace the literal 'InsertFileNameHere' with the destination you want for the trace file (don't add .trc to the end of the file name - it will automatically get added for you). It should look like this:
/****************************************************/ -- Create a Queue -- Please replace the text InsertFileNameHere, with an appropriate exec @rc = sp_trace_create @TraceID output, 0, N'C:\Trace Files\OLAP Error SQL Data', @maxfilesize, NULL -- Client side File and Table cannot be scripted -- Set the events -- Set the Filters exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 1745445d-46a5-4050-9922-16caf3851690' -- display trace id for future references error: finish: |
Now you're going to add a couple of steps after the step you want to trace...
(7) You need to stop the trace. Add this code to a T-SQL step named 'Stop SQL Trace', note that the file name in the WHERE clause must match the file name you specified above in the 'Start SQL Trace' step, with the '.trc. added to the end:
DECLARE @TraceID INT IF @TraceID IS NOT NULL EXEC sp_trace_setstatus @TraceID, 0 END |
(8) If you want to keep generations of the Trace file, you can add this to another step, named 'Rename SQL Trace File', to add a date-time stamp to the filename:
| EXECUTE [sp_RenameFile] @Folder = 'C:\Trace Files\' ,@FileName = 'OLAP Error SQL Data.trc' ,@NewFileName = NULL ,@DateTimeType = NULL ,@Debug = 0 |
This requires the proc sp_RenameFile, which I have posted under Scripts on SSC.
In order to enumerate the running traces, you can use this code:
| SELECT * FROM :: fn_trace_getinfo(0) |
Analysis Services Trace
(1) Create the Trace you want in SQL Profiler, on an Analysis Services connection, setting a Trace Name (I used 'OLAP Error OLAP Data'), Save To File with a file name and a file size large enough to hold the data in a single file (I used 5MB), and the Events / Data fields you want to trace. Run the Trace.
(2) Stop the Trace - we don't actually want to collect anything at this point.
(3) Export the trace definition - select "File|Export|Script Trace Defintion|For Analysis Services 2005...". Export the definition to a .XMLA file of your choice.
(4) Close Profiler, we're done with it at this point.
In the SQL job with the step you want to trace, you're going to add a couple of steps before the step you want to trace...
(5) Add the following code to a T-SQL job step, named 'Delete OLAP Trace File', before the step you want to trace:
| DECLARE @OS_Cmd VARCHAR(1024) SELECT @OS_Cmd = 'DEL /Q /F "C:\Trace Files\OLAP Error OLAP Data.trc"' EXECUTE master.dbo.xp_cmdshell @OS_Cmd |
(6) Add the code from the SQL file you just created to an SQL Server Analysis Services Command job step, named 'Start OLAP Trace', immediately after the 'Delete OLAP Trace File' step. Remove the <?xml version="1.0" encoding="utf-8"?>, and add a LogFileName line, the result should look like this:
| <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ObjectDefinition> <Trace> <ID>OLAP_Error_OLAP_Trace</ID> <Name>OLAP_Error_OLAP_Trace</Name> <LogFileName>OLAP_Error_OLAP_Trace.trc</LogFileName> <Events> <Event> <EventID>15</EventID> <Columns> <ColumnID>28</ColumnID> <ColumnID>32</ColumnID> <ColumnID>36</ColumnID> <ColumnID>44</ColumnID> <ColumnID>1</ColumnID> <ColumnID>25</ColumnID> <ColumnID>33</ColumnID> <ColumnID>37</ColumnID> <ColumnID>41</ColumnID> <ColumnID>45</ColumnID> <ColumnID>2</ColumnID> <ColumnID>42</ColumnID> <ColumnID>3</ColumnID> </Columns> </Event> </Events> <Filter> <NotLike> <ColumnID>37</ColumnID> <Value>SQL Server Profiler - 54043a84-7877-4b36-b048-e70f72bf42cf</Value> </NotLike> </Filter> </Trace> </ObjectDefinition> </Create> </Batch> |
NOTE - the file name you specify in the LogFileName parameter will be generated in the OLAP Log folder for the AS instance, e.g. C:\Microsoft SQL Server\MSSQL.2\OLAP\Log, you have no control over this location.
Now you're going to add a couple of steps after the step you want to trace...
(7) You need to stop the trace. Add this code to a SQL Server Analysis Services Command step named 'Stop OLAP Trace':
| <Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <TraceID>OLAP_Error_OLAP_Trace</TraceID> </Object> </Delete> |
(8) If you want to keep generations of the Trace file, you can add this to another step, named 'Rename OLAP Trace file', to add a date-time stamp to the filename:
| EXECUTE [sp_RenameFile] @Folder = 'C:\Microsoft SQL Server\MSSQL.2\OLAP\Log\' ,@FileName = 'OLAP_Error_OLAP_Trace.trc' ,@NewFileName = NULL ,@DateTimeType = NULL ,@Debug = 0 |
This requires the proc sp_RenameFile, which I have posted under Scripts on SSC.
In order to enumerate the running traces, you can execute this code, after connecting to the AS instance, and opening an XMLA Query window:
| <Discover xmlns="urn:schemas-microsoft-com:xml-analysis"> <RequestType>DISCOVER_TRACES</RequestType> <Restrictions> <RestrictionList> </RestrictionList> </Restrictions> <Properties> <PropertyList> </PropertyList> </Properties> </Discover> |
Conclusion
In conclusion, I'm not an XMLA / ASSL expert (add one more area where I need more practice), but I searched high and low for this information and wanted to share it as a cook-book style write-up. Oh, and many thanks to Jon at MS for his help in figuring this out, I wouldn't have got it without his help.
If you want to get into Analysis Services Scripting Language (ASSL) in more depth, it's in SQL 2005 BOL, or you can start here: http://technet.microsoft.com/en-us/library/ms128525.aspx.
