Querying and Reporting on Report Execution Log Data
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, you should extract the log data and store it in a separate local report execution log database that you create.
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.
How to Get Reporting Services Samples
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 https://www.CodePlex.com. You can also click SQL Server Samples to go directly to the Microsoft SQL Server Samples and Community Projects portal page.
On the Microsoft SQL Server Product Samples page, click Reporting Services.
Click the Release tab.
Download the SQL Server Samples Setup program and then run Setup on your computer. You must install all the samples. There is not a separate Setup program for Reporting Services.
Reporting on Execution Log Data
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
The package, script, and report files are installed through Server Management Report Samples. For instructions on how to use the sample package and reports, see Server Management Report Samples.
Refreshing Execution Log Data
To refresh the report execution log database with more recent data, run the RSExecutionLog_Update.dtsx package periodically. The package appends new log entries to the existing entries. It does not remove old entries or historical data. Examples of historical data might include users who no longer run reports on a report server, computer names that are no longer in service, or reports that no longer exist. If you do not want to save historical execution log data, you can periodically run the Cleanup.sql query on the report execution log database.
In SQL Server Management Studio, select RSExecutionLog from the Available Databases list box on the tool bar.
Click File, point to Open, and then click File. By default, the file is located at \100\Samples\Reporting Services\Report Samples\Report Execution Log Sample Reports\Cleanup.sql. Select that file, and then click OK.
Click Execute on the toolbar to run the query.