Reporting Service Execution Log Retention

The default logging retention value is 60 days in SSRS. If you want to modify the retention period, you could use SQL Server Management Studio to connect to SSRS instance, check the Advanced page of Server Properties, and change the value of ExecutionLogDaysKept.

From the GUI it reads “Valid values for this property include -1 and 0 through 2,147,483,647. If the value is 0 entries are not deleted from the Execution Log Table. ” SSRS Product Group confirm there is a known issue that only -1 will keep the log indefinitely. Value 0 means keep 0 day of log; the log will be deleted when the retention job is trigger.

 

Every day at 2AM local time, the retention job will be triggered from each SSRS server to archive the log. Stored Proc ExpireExecutionLogEntries in SSRS catalog database is called. If you change the SSRS web/app server time zone setting, you should restart SSRS web/app server to make the retention job running in the right time.

 
Let’s check the coding of this stored proc:

[sql]
if exists (select * from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept' and CAST(CAST(Value as nvarchar) as integer) = -1)
begin
return
end

delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) >= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept')
[/sql]

You will see only -1 could keep the log indefinitely.
 

For a heavy production environment, we don’t recommend you keeping the log forever. Because it will increase the size of SSRS’s catalog database. In my testing environment, size of log table with 1,572,866 log entries is 900MB. When the retention job is running to delete huge number of log, it takes time and there will be exclusive table lock on table ExecutionLogStorage. No report generation will be done before the lock is released. In my testing environment (Azure Standard D2 v2 VM), it takes 112 seconds to delete 1,572,866 log entries. And it causes report execution error. So you should avoid schedule the regular report subscription at 2AM.

 

Posted by Shiyang Qiu, Sep 12, 2016
Special thanks to Chris Finlan

Comments

• Anonymous
  February 15, 2018
  The comment has been removed
  • Anonymous
    February 20, 2018
    Hi Fadiel, I have no hint for this, it may happen for reason like #1 the parameter was changed previously, #2 someone deleted the logs...