Find out about reports that are being executed on your SSRS Server

Are you being asked questions about reports running on your SSRS servers?

• When did an SSRS Report Last Run?

• Who is running reports?

• How long did a report take to run?

• How many records were produced by a report?

• What parameters were passed to a report?

• Was the report run interactively or scheduled?

• Did execution succeed?

• Was the report executed or just rendered?

• What render format was produced?

SSRS provides a log file which is a text file and can be viewed at any time, but there are also some views provided in the ReportServer database:

The SSRS Execution Log View

The latest version of the views is dbo.ExecutionLog3. The following screen shots shows the first 14 records from a test SSRS server ExecutionLog3 view:

The first few columns show the client host and user that requested the report along with the pathname of the report, whether it was run interactively and the render format requested. In these examples we can see RPL as the format, but we would also see, PDF, HTML, EXCEL, WORD, etc. here.

The next few columns show a list of parameter values passed in for parameter reports, whether the report was fully executed or just rendered (as in the case of cached reports and those rendered from snapshots), the start and finish execution times, and a summary of how long it took to retrieve data, process the report and render the report – these can be helpful to determine if caching reports would be beneficial).

The last few columns show whether the source of the report was a live execution or a history snapshot. We can also see the size of the report and number of records processed.

The very last column is an XML column containing additional information as shown in the following example:

The SSRS Log File

The SSRS log file can be found in C: Program Files Microsoft SQL Server MSRS12.SQL2014 Reporting Services LogFiles (for SQL Server 2014).

Log file settings control how verbose the log file content is and the maximum size that the file can grow to.

Here is a sample from the start of an SSRS log file:

The following extract matches rows 12 to 14 from the execution log view:

Configuring The SSRS Log File Settings

The SSRS Log File settings are controlled through the following XML configuration file:

C: Program Files Microsoft SQL Server MSRS12.SQL2014 Reporting Services ReportServer bin ReportingServicesService.exe.config

Here is the  file content:

The RSTrace element of the file defines the default maximum file size of 32Mb, and a keep time of 14 days.

The following line sets the level of verbosity:

A value of 3 is the default. A value of 4 would set the level of logging to more verbose, and a value lower than 3 will be less verbose.

Only increase the level to 4 for troubleshooting as many more lines of log data will be captured and this will result in log files growing rapidly, but may also impact on server performance.

If you would like to learn more about SQL Server Reporting Services (SSRS) take a look at the following course:  https://www.ptr.co.uk/ssrs.