Share via

SQL Server Reporting Service - ExecutionLog

  • Article

How to view Execution Log ?

There is a table executionLogStorage in the report server which hold the execution log detail

Microsoft recommends to use the view for querying the execution log instead of tables. there are three different views ExecutionLog,ExecutionLog2,ExecutionLog3.

ExecutionLog is for backward compatibility and available with SQL 2008 onward.

ExecutionLog2 is introduced with SQL 2008 and ExecutionLog3 is introduced with SQL 2008 R2.

ExecutionLog2  and ExecutionLog3 is similar , With Log3 having few columns renamed e.g. ReportPath is now ItemPath,ReportAction is now ItemAction

Lazy people can copy the sql statement from here :), this sql will work for SQL 2008 onwards version

select * from ExecutionLog

select * from ExecutionLog2

select * from ExecutionLog3

How we can check  Execution Logging is enabled or not ?

There is a Configuration tables in the report server which hold the value of this setting, You can use the below sql statement to get the current retention period for the Execution Log.

Use [ReportServer]

go

select * from ConfigurationInfo where Name='EnableExecutionLogging'

go

Alternate way to enable Execution Logging is

Start SQL Server Management Studio

Connect to the Report Server Instance.

Right-click the server name and click Properties.

Click the Logging page.

Change EnableExecutionLogging to True.

How to validate the retention period for the Execution Log ?

Configuration tables in the report server hold the value of this setting,if you have enough hard disk space nothing else need to worry about.

You can use the below sql statement to get the current retention period for the Execution Log.

Use [ReportServer]

go

select * from ConfigurationInfo where Name='ExecutionLogDaysKept'

go

By default, log entries are kept 60 days.

How to purge the Execution Log Manually ?

You can use the below sql statement to manually purge log entry. I've deleted the record in chunk so not affect the performance.

Use [ReportServer]

Go

declare  @rslog table

(LogEntryId bigint)

insert into @rslog

select top 50 LogEntryId from [ExecutionLogStorage] with (nolock) where TimeStart <DATEADD(day,-5,GETDATE()) order by TimeStart desc

delete from [ExecutionLogStorage]   where LogEntryId in (select LogEntryId from @rslog)

Please note If you modify the setting in the report server database, you need to restart the service before the changes take effect.