Monitoring Reporting Services

  • Article

Reporting services, like many services, logs what is going on to to a log file, in this case the RS Report Execution Log.  Scanning that log for activity is a pain, so it would make sense to be able to query that and properly report on what's going on. It would be rude not to use Reporting Services to do this even if does seem to be case of going round in circles.  The trick is to load the data into SQL first and then run the reports over that.  Since the layout of the logs is defined, this is a standard bit of work and this can be found on codeplex, along with a load of other useful Reporting Services samples. 

The sample project for looking at the execution log is available for SQL Server 2005 here  and SQL Server 2008 here and comprises:

Object 

Description

Createtables.sql

Creates the table structure for storing report execution log data.

Cleanup.sql

Removes data from the tables so that you can refresh the tables with new data.

RSReportExecutionLog_Update.dtsx

An Integration Services package to extract internal data from the report server database and copies it to a user-defined database that contains report execution log data.

RSReportExecutionLog_Update.dtsConfig

Specifies settings for the above package.

Execution Status Codes.rdl

Shows the success and failure rates for all report executions occurring within a specified date range. It also shows which failure codes occurred, and which reports failed to process.

Execution Summary.rdl

Shows overall report execution statistics for a specified date range. It shows the number of reports processed each day, the top 10 most requested reports, and the top 10 longest running reports.

Report Summary.rdl

Shows summary report execution information for a specific report within a specified date range.

One tip is to change the queries in these management reports to exclude the names management reports themselves from the reports otherwise you will see an entry every time you run one of these reports and that is really going round in circles!

Thanks to Dave Wickert from the SQL BI Product Unit for reminding me about this.

Technorati Tags: SQL Server 2008,SQL Server 2005,Reporting Services