4

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.

2

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.

  • I made essentially the same change, although I went with a more modest 10 years. It's definitely an easy workaround, provided you're aware that the issue even exists. – Bob C Mar 23 '16 at 18:04
0

Yes, the -1 is the correct setting for ExecutionLogDaysKept. It is what I have mine set to.

The MSDN documentation is incorrect for 2012 and earlier but for 2014 and later it is correct. Go to Server Properties (Advanced Page) - Reporting Services which shows the setting for 2016. If you change the version to 2014 you'll see it also shows -1. But when you go to 2012 or 2008 it states it is 0, which is not correct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.