Reporting Service Execution Logging

- The Execution Log contains information of Report that executes across multiple servers in a scale-out (also in non scale out scenarios)

- A cap can be placed on the number of days of logging information kept

– This process is called ‘pruning’ and occurs each day at 2 am

Aimed at:

– Report Server administrators looking to identify reports that need to be cached in some manner

– Report Publishers looking to improve performance of reports: queries, layouts, and expressions

Information is stored a table called ExecutionLog in the ReportServer database. Important columns include:

- Name of the Report

- Rendering Format

- Parameter Values

- Time spent on data retrieval,processing and Rendering

- Source of data ( live or cached or snapshot)

- Do not query the table directly without nolock hint as it could cause concurrency problems.

- To review execution logging data, the information must be extracted and queried – DTS package and sample reports are shipped to help facilitate this process. (Check the Reporting Services Books online for step by step process)

ExecutionLog Table

reportID and userID	Identifier to the report and user.
Format	Requested rendering format
parameters	null is report parameters were not used
timedataretrieval	Time (ms) spent retrieving data.
timeprocessing	Time (ms) spent processing.
timerendering	Time(ms) spent rendering.
source	1=Live,2=Cache,3=Snapshot,4=History
status	Completed status, rsSuccess or error code.

SQL Statement which will give the Name of the REPORT and its Execution details (For 2005):

Select CAST(C.Name AS VARCHAR(20)) [Name],
E.ReportID,
E.InstanceName,
E.UserName,
E.RequestType,
E.Format,
E.Parameters,
E.TimeStart,
E.TimeEnd,
E.TimeDataRetrieval,
E.TimeProcessing,
E.TimeRendering,
E.Source,
E.Status,
E.ByteCount,
E.[RowCount]
from executionlog E WITH (NOLOCK) inner join catalog C WITH (NOLOCK)   
on E.ReportID = C.ItemID

Try out the difference between the above and this one:

select * from executionlog WITH (NOLOCK)

SQL Statement which will give the Name of the REPORT and its Execution details (For 2008):

Select CAST(C.Name AS VARCHAR(20)) [Name],
E.ReportID,
E.InstanceName,
E.UserName,
E.RequestType,
E.Format,
E.Parameters,
E.TimeStart,
E.TimeEnd,
E.TimeDataRetrieval,
E.TimeProcessing,
E.TimeRendering,
E.Source,
E.Status,
E.ByteCount,
E.[RowCount], E.[AdditionalInfo] from executionlogstorage E WITH (NOLOCK) inner join catalog C WITH (NOLOCK) on E.ReportID = C.ItemID

More Information:

https://msdn.microsoft.com/en-us/library/aa972246.aspx

Comments

• Anonymous
  February 10, 2009
  MSDN states that the "status" in the RS ExecutionLog table will contain either rsSuccess or the appropriate error code on failure: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_rslogfiles_v1_7942.asp However, regardless of what error (missing ds, bad syntax etc.) I cannot seem to trap anything other then rsProcessingAborted (which is logged for all errors).  I have enabled Remote errors via the ConfigurationInfo tabl; am I missing something? Thanks

• Anonymous
  May 07, 2009
  What kind of information you're looking to trap here? if you're looking for a detailed error message etc, then this is not the place to look for. You need to look in to the Reporting Service log files folder, located in the installation directory of reporting service.