SQL Server 2008 R2 Reporting Services keeps a log of who runs what report, stored in the ExecutionLogStorage table of the ReportServer database. The default retention period for that history data is 60 days.
A few months back, I wanted to increase that retention period on several of my reporting servers, so I searched for how to do it and found a couple of different blog posts which said that you update the ConfigurationInfo table in the ReportServer database. Specifically, they said you set the Value column to the number of retention days for the row where Name = 'ExecutionLogDaysKept', and that if you set the value to 0, execution history will be kept indefinitely. So that's what I did, and promptly forgot about it.
Fast-forward to this morning, when I ran a who-launched-what-report-when query against the ExecutionLog view, and promptly got back no rows. While investigating, I learned about an SSMS GUI with which you can also manage report-execution history retention; the GUI evidently updates the same ConfigurationInfo row, but its labels make it fairly apparent that setting the 'ExecutionLogDaysKept' value to 0 actually translates to, "delete execution-log entries older than 0 days," or basically, "don't keep any history prior to today" — the diametric opposite of "keep history indefinitely."
This is borne out in an examination of the stored procedure ExpireExecutionLogEntries, which references the 'ExecutionLogDaysKept' value, and in which the comments and the code itself clearly show that the correct value to indicate indefinite history retention is -1, not 0.
The authors of those blogs shall remain nameless, since they were SQL folks with whom I was not especially familiar and whose advice I therefore shouldn't have followed so blithely. I suppose this post is mostly a caveat not to make the same mistake I did; however, I do have a couple of questions:
- Has anyone else experienced the same thing?
- Does report-execution history retention management work the same way in
subsequent versions of SSRS?
Edit: Apologies to those unnamed blog authors, as it appears that the erroneous retention-setting info actually originates in at least one location in Books Online for 2008R2. It's still present in BOL for 2016 — not sure whether there's still a disconnect with the underlying procedure code. Submitted a Microsoft Connect entry requesting that the documentation be corrected.
Best Answer
Interesting find. I was unaware of this setting until I built a page that used the SSRS execution history table as the sole source of a report's download count. To my surprise, the counts disappeared one day. At that point, I discovered the default value of 60 days retention. I recall increasing the retention setting in the GUI, but I did not set to 0.
I re-engineered my download count to be stored in a custom table and updated daily via a SQL Agent job using the SSRS history, in order to eliminate any other surprises. There is definitely conflicting documentation on what "0" accomplishes. However, if you set the retention days to a very large number (several hundred years), then that is essentially an infinite retention period and should accomplish your goal.