Tuesday 17 February 2015

ReportServer.dbo.ExecutionLogStorage table only shows a couple of months back

In my workplace we use the reports based off the SSRS Reporting Services database ExecutionLogStorage to identify report usage and alert us to report execution failures, in the form of the below statement to identify how often different reports have been used, how long they take to run, and how often they fail:

select Catalog.path, ExecutionLogStorage.*
from ExecutionLogStorage
inner join Catalog
on ExecutionLogStorage.ReportID = Catalog.ItemID

Unfortunately, on a server that went online some-time last year, the report execution logs are very limited. After doing some research I found the answer here (Microsoft TechNet article).

If the query below returns anything other than -1, your report execution logs will be being deleted after the resulting number of days.

select *
from ConfigurationInfo
where name = 'ExecutionLogDaysKept'

If it is, update it to -1 to stop historical logs from being deleted.

As to how to restore the deleted ExeuctionLogStorage entries, you'll need to restore a copy of the database to a temporary location, and run insert operations to re-add those rows to the table.