Reporting Services stores execution data in several tables in the report server database. Because the internal table structure does not present report execution log data in a format that is accessible to users, a view was created: ExecutionLog. In Sql Server Reporting Services 2008 this view was extended to a new view: ExecutionLog2.

The report server execution log contains information about the reports that execute on the server or on multiple servers in a scale-out deployment. You can use the report execution log to find out how often a report is requested, what formats are used the most, and how many milliseconds of processing time is spent on each processing phase.

If you want to go a step further, you could extract the log data and store it in a separate local report execution log database that you create. This way you could extract data from the ExecutionLog view and store it into a separate database. Then you could create performance & execution related reports on that database.

And here's something great: Microsoft provides you with everything you need to create & implement such a solution, for free !

• All of the sample files used to query and view report execution log data are included in the Reporting Services report samples on the CodePlex site at http://www.CodePlex.com

To view the report server execution log, you can use the following:

• A sample script file called Createtables.sql that creates a new execution log database specifically for viewing log data.
• A sample SQL Server Integration Services package called RSExecutionLog_Update.dtsx that extracts data from the internal report server database and copies it to the sample execution log database that you create.
• A collection of sample reports that retrieves data from the execution log database and displays it in reports that you view on a report server.

Samples reports are also included that retrieve report execution data from the local report execution log database. The reports work with a local database that contains execution log data. Once you create the database, you can run the package to fill it with data, and then run the reports to view information about report activity.

The following files are used together to create the database and extract data:

• RSExecutionLog_Update.dtsx
• RSExecutionLog_Update.dtsConfig
• Createtables.sql

The following sample reports provide information about report server activity:

• Execution Status Codes.rdl
• Report Summary.rdl
• Execution Summary.rdl

Examples:

 

Comments

[collaboration solutions]

nice post, this is what i was looking for in the first place

[johnpaul]

We installed this on 2005 3 years ago and it produced just the reports we needed (for 1 week) but broke our system (obscure error visible in xml - can't recall exactly what it was but connected directly to the RSExecution installation). Solution was to re-build from backups. Has anyone else experienced issues installing this? - the reports were good while they lasted and would like to attempt this again on 2008 R2 - however I am now a little nervous.
Thanks, JP