Hello,

Welcome back! As 2017 begins, things are going to change around here. A new challenge has arrived, #22posts12months. I will try to keep posts shorter and objective. I will publish every 15 days. Keep me updated on how it all works for you!

ExecutionLogDaysKept

Probably you didn’t noticed yet but if you try to collect usage information for our Microsoft SQL Server Reporting Services (execution log), by default Reporting Services instance will only keep the most recent 60 days in the execution log . Even though the server had been running a good deal longer than that!

Parameter: ExecutionLogDaysKept

The number of days to keep report execution information in the execution log. Valid values for this property include 0 through 2,147,483,647. If the value is 0 entries are not deleted from the Execution Log table. The default value is 60.

Source: https://technet.microsoft.com/en-us/library/bb934303(v=sql.105).aspx

How to keep them then?

There’re 2 ways to do this: (1) through Server Properties page or (2) a SQL statement. 

1. The Server Properties (Advanced Page) – Reporting Services MSDN page gives the details of this, and many other advanced server configuration parameters. To keep your execution log, please follow the next steps.

Steps:
1. Start SQL Server Management Studio
2. Connect to a report server instance
3. Right-click the report server name  and select Properties.
4. Click Advanced to open this page.
5. Update value to ‘0’. The value is the time in days for log entries to be kept. Valid values are in the range: 0 to 2,147,483,647, where a 0 indicates that entries are never deleted.

6. Executing the following stored procedure will force the table to truncate as expected, based on the ExecutionLogDaysKept value:

 EXEC ReportServer.dbo.ExpireExecutionLogEntries

2. The parameter is an entry of table ‘ConfigurationInfo’. To keep your execution log, please follow the next steps.

Steps:
1. Start SQL Server Management Studio
2. Connect to a report server instance
3. Execute the following query:

UPDATE ConfigurationInfo
SET Value = '0'
WHERE Name = 'ExecutionLogDaysKept'

4. Executing the following stored procedure will force the table to truncate as expected, based on the ExecutionLogDaysKept value:

EXEC ReportServer.dbo.ExpireExecutionLogEntries

See you on the next post!

Thanks,
RMC