SSRS execution log expiration settings

Short description to change execution log expiration settings. SSRS execution log is good tool to monitor reports activity. When using it you can find that the data are not stored forever. The default time period for which the data are stored in Reporting Services database are 60 days. If you would like to change this setting, follow these steps.

Open SQL Management Studio and connect to Reporting Services by selecting Server type to Reporting Services and select your server name.

Connect to SSRS
Connect to SSRS

When connected to the server go to Object Inspector, obviously place on left side in Management Studio, go to Report Server name at the top and right click to get Popup menu.

Server properties
Picture 2 – Server properties

Go to Properties to get Server Properties window. Here you can see set of Reporting Services settings you can change from Management Studio. If you look at the first section, General, thee is URL address for report server (it is available in Reporting Services Configuration manager too). The name of instance is visible when you go to Reporting Services Manager at the top of the web interface. You can change it here.

But we would like to change Execution log settings so go to Logging section on the left menu.

SSRS general properties
Picture 3 – SSRS general properties

Here we can set the number of days the data will be stored in execution log. Or if to disable Report Execution logging at all.

SSRS execution log settings
Picture 4 -SSRS execution log settings

Reporting Services database log execution

Reporting Services execution log. You will find it in Reporting Services database. There stored data regarding Reports and Users activity in Reporting Services repository. You can use it for user access monitoring or when you register problems with Report performance.

Execution log data are stored in ExecutionLogStorage table or you can access it better using view Dbo.ExecutionLog, dbo.ExecutionLog2, dbo.ExecutionLog3 where this table is joined with Catalog table. Here are briefly few interesting attributes which could suit you.

  • Report data processing – information you need to get when resolving performance issues
    • TimeDataRetrieval
      • Time the data are retrieved from dataset in Milliseconds (SQL query, SQL procedure, etc.)
    • TimeProcessing
      • Time the data are processed in report (Expressions, Groups, Sorting etc.)
    • TimeRendering
      • Time the report spends with rendering (Pictures, rows, etc.)
    • TimeStart
      • Start of report processing
    • TimeEnd
      • End of report processing
  • Report data retrieval – good to know how many rows are taken from datasets to report
    • ByteCount
      • Size of rendered report in bytes
    • RowCount
      • Number of rows returned from datasets for report rendering
    • AdditionalInfo
      • Additional information regarding report rendering, processing (for example, rendering extension.)
  • Report states
    • Source – it could be useful when tuning report performance. For example, if report data are already taken from cache.
      • From which source was report generated
        • Live (preview report)
        • Cache – datasets are taken from execution cache
        • Snapshot – report is taken from snapshot
        • History
        • AdHoc – drilltrough actioin, report builder preview
        • Session – data are exported to cs vor excel format for example
        • Rdce – Rdce extension
    • ItemAction – to monitor source of action
      • Render, Sort, BookMarkNavigation, DocumentNavigation, GetDocumentMap, Findstring, Execute, RenderEdit
    • Status – here you can monitor wheater the report was generated sucesfully
      • Status of report processing – error code or rsSuccess
  • Catalog info
    • ItemPath
      • Report item with its path
    • Format
      • In which format was report generated
    • Parameters
      • Parameters set due to report execution – helpful when you would like to monitor which parameters caused performance issues or for nice solution to get last parameter value user set
    • UserName
      • User identifier

Let’s see stats f the Sales Report

FROM [ReportServer].[dbo].[ExecutionLog3]
WHERE ItemPath ='/Blog/ReportSales0 - Detail data'
AND TimeStart='2019-07-28 16:28:12.630'
BY TimeStart DESC.

You can see on picture bellow parameters the report was running and duration of report processing.

Report stats
Picture 1 – Report stats

Here you can see time in milliseconds the report spent on Time Processing and Time Rendering. Time Data Retrieval is 0 because the report was processed from cache as you can see in Source attribute.

Report starts
Picture 2 – Report starts