BOL instruction on SSRS report-execution log retention is wrong - by BChristensen23

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 2506779 Comments
Status Resolved Workarounds
Type Suggestion Repros 0
Opened 3/23/2016 10:40:29 AM
Access Restriction Public

Description

SQL Server Reporting Services keeps a log of who runs which reports, 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  ReportServer. Specifically, they said to set the Value column to the number of retention days for the row where Name = 'ExecutionLogDaysKept', and that if the value is set to 0, execution history will be kept indefinitely. So that's what I did, and promptly forgot about it.

This morning, I ran a query against the ExecutionLog view, and got no rows back.  In the course of an initial investigation, 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.

Upon further review, it appears that the erroneous retention-setting instructions actually originate in at least one location in Books Online for 2008R2 (https://msdn.microsoft.com/en-us/library/bb934303%28v=sql.105%29.aspx).  The documentation error is still present in BOL for 2016 (https://msdn.microsoft.com/en-us/library/bb934303.aspx); I have not yet installed a CTP of the 2016 engine to determine whether there's still a disconnect between the documentation and the underlying procedure code.  If there is, the documentation should be amended.  With luck, that will prevent anyone else from losing their entire report-execution history.

Sign in to post a comment.
Posted by Microsoft on 3/16/2017 at 2:46 PM
I reviewed this item. Thank you for the feedback. The 2016 documentation did indicate that a value of -1 will make sure that entries are not deleted. I added a note to call out that a value of 0 will delete the entries to try and raise awareness of that fact.