SQL Server Reporting Service (SSRS) usage continues to increase. DBAs, regardless of their involvement in implementing SSRS reports, will eventually need to investigate why a report is failing. A feature has been added to SQL Overview to aid them. This feature checks for report and subscription failures along with collecting diagnostic information.

SSRS Tables and View

SQL Server has three ReportServer tables/views that provide report execution log, subscription history, and execution data. The report execution log is collected once a day. However, the subscription and execution data tables do not maintain history, so data is collected every 30 minutes.

ExecutionLog system table/view by default keeps the last 60 days of report executions. This setting is configurable by updating the ReportServer ConfigurationInfo table.  The ExecutionLog table's status column reflects the results of each report execution. Each day, the SQL Overview package retrieves the last 48 hours of report execution logs. During the automated review of this data, any errors detected since the previous review are emailed as a report.

In SQL Server 2005, this was a table and in SQL Server 2008, it became a view. They both contain the same columns and are processed the same way.

The subscriptions system table contains the status and time of the last execution for each subscription. It does not keep a history of subscription executions. To create a history of subscriptions execution data is collected from this table every 30 minutes using a new SSIS package. If you have a subscription running more often than every 30 minutes, then you need to schedule this new SSIS package accordingly.

A 45 day history of subscription executions is kept in SQL Overview. You can adjust this setting in the package. Each morning a report is emailed for any failed subscriptions detected since the previous daily report.

You might think when a subscription fails the report would also be flagged as failing. But that is not the case. A subscription can successfully execute a report but fail when it tries to email it. When this occurs, the subscription will have an unsuccessful last execution status but the report execution log will appear successful. This happens when a subscription has an invalid email address. In addition, an invalid email address prevents anyone else on the subscription's email list from getting the report even if the other email addresses are valid.

The notifications table contains data for executing subscriptions. When there is trouble with a report or subscription, this data might prove helpful. The data is collected using the new SSIS Subscription package. It is currently only collected for use in potential troubleshooting. SQL Overview keeps this data for 45 days unless configured otherwise in the package.

Collecting ExecutionLog

The ExecutionLog table/view is collected using the existing SQL Overview package. The ReportServer database does not exist on every instance. On named instances, the database name has the instance name is appended to it. For example, on the named instance S2008, the database name is ReportServer$S2008.

To handle instances with no ReportServer database or if a different database name is used, a temp table is created on each instance by the package. If the ExecutionLog table does not exist, the temp table remains empty. Otherwise, it is loaded with rows created in the last 48 hours. By using this temp table, the package can easily process instances with and without a ReportServer database. This technique is also used when collecting data from the other ReportServer tables.

Update SSIS Package

Create the new temp table using the attached SQL file 3-TempdbTables.sql

Create the new SQL Overview table  using the attached file ReportServices_ExecutionLog.sql

Using SQL Server Business Intelligence Development Studio, edit SQL_Overview_Package.dtsx and add the following containers.

Truncate Table Job_Activity

• Add "Execute SQL Task" object to the Truncate Tables container
• Settings - double click on icon
• • Name: Truncate SSRS Report Execution Log
  • Connection: QASRV.SQL_Overview
  • SQL Statement: TRUNCATE TABLE dbo.ReportServices_ExecutionLog
  • BypassPrepare: False

Collect Job Activity

1. Add "Foreach Loop Container" between "Collect Job History" and "Collect Table Sizes"
   1. Connect the " Collect Job History" task to this item with the green line/arrow and remove the connection to "Collect Table Sizes"
   2. Settings
      1. General
         1. Name: Collect SSRS Report History
      2. Collection
         1. Change Enumerator to Foreach ADO enumerator
         2. Select ADO object source variable User::SQL_RS
      3. Variable Mapping
         1. Add User::SRV_Conn
      4. Click OK
   3. Right click on this container
   4. Select Properties
   5. Set MaximumErrorCount to 999
2. Add "Execute SQL Task" to the "Foreach Loop Container"
   1. Double click icon > Settings: General
      1. Name: Get SSRS Execution Log
      2. Connection Type: OLE DB
      3. Connection: MultiServer
      4. SQLSourceType: Direct input
      5. SQLStatement:  Add the contents of the SQL file temp_ReportServices_ExecutionLog.sql
      6. Click OK
3. Add "Data Flow Task" to the "Foreach Loop Container"
   1. Connect the green arrow from "Get SSRS Execution Log" to it
   2. Rename to Load SSRS Report History
   3. Right click on this task
   4. Select Properties
   5. Set MaximumErrorCount to 999
4. Select the Data Flow Tab or double click on icon for "Data Flow task"
5. Add "OLE DB Source" from toolbox. This is used to read the TEMPDB table on the remote instance.
   1. Double click icon
      1. OLE DB Connection manager: MultiServerTempdb
      2. Change Data access mode to Table or View
      3. Name of the table or the view: [dbo].[temp_ReportServices_ExecutionLog
      4. Click Preview to verify and then click Close when done
      5. Click OK
6. Add "OLE DB Destination" from toolbox. This is used to store the data in the local instance.
   1. Connect the "OLE DB Source" to this element with the green line/arrow
   2. Double click on the Icon for OLE DB Destination and make the following changes
      1. OLE Connection Manager: QASRV.SQL_Overview
      2. Name of the table or the view: [dbo].[Job_Activity]
      3. Click Mappings and confirm that the columns mappings are correct.
      4. Click OK
7. Connect a green line from "Collect Job Activity"  to "Collect Job History".
8. Remove the green line directly connecting containers "Collect Backup History" and Collect Job History"

If you rather not update an existing package, you can use the complete package attached to this article.

Collecting Subscriptions and Notifications

To collect the contents of the ReportServer tables Subscription and Notifications, the new package "SQL_Reporting_Subscriptions.dtsx" is used. There are temp tables used in this package and should exist on the local instance prior to executing the package. Use the attachedSQL files (3-TempdbTables.sql) to create the tables. The contents of the Subscription and Notification tables are collected multiple times a day. The new SSIS package is used to capture all Subscription status  changes and notification table’s content. Any duplicate row in either table is deleted from the SQL Overview database. The rest of the data is kept for 45 days. This can be adjusted by modifying the delete SQL in the package.

The SQL Overview subscription history table contains one of three types of data to make it easier to join with the execution history.

1) A row for each time the last status column has changed

2) Last executed subscriptions if no current execution is found

3) Reports with no subscription

Originally, I tried to extract only the rows that changed for a subscription. This proved difficult. Instead, I just extract everything then delete any duplicate rows and keep the rows with the latest collection date. This SQL is based on the post SQL SERVER – 2005 – 2008 – Delete Duplicate Rows by Pinal Dave.

Delete Duplicates SQL

/* Delete Duplicate records */WITH CTE ([Server]
      ,[ReportName]
      ,[ReportPath]
      ,[Type]
      ,[LastRun]
      ,[ScheduleId]
      ,[SubscriptionId], DuplicateCount)
AS
(
SELECT [Server]
      ,[ReportName]
      ,[ReportPath]
      ,[Type]
      ,[LastRun]
      ,[ScheduleId]
      ,[SubscriptionId]
,ROW_NUMBER() OVER(PARTITION BY [Server]
      ,[ReportName]
      ,[ReportPath]
      ,[Type]
      ,[LastRun]
      ,[ScheduleId]
      ,[SubscriptionId]
 ORDER BY Collection_date desc) AS DuplicateCount
FROM [ReportServices_SubscriptionsLastStatus]
)
DELETE 
FROM CTE
WHERE DuplicateCount > 1

Tables

We have the following new tables:

• dbo.ReportServices_ExecutionLog - Stores the last 48 hours execution log history from the ReportServer ExecutionLog table/view.
• dbo.ReportServices_Notifications - Stores the contents of the SSRS Notification table for troubleshooting.
• dbo.ReportServices_SubscriptionsLastStatus - Stores any changes to the subscription rows. It contains three types of rows.

  1) A row for each time the last status column has changed

  2) Last executed subscription if no current executions are found

  3) Report with no subscription

• dbo.SSIS_Errors_Reporting_Subscriptions - Stores errors encountered while executing the package SQL_Reporting_Subscriptions.dtsx

Reports

This package comes with three new reports that are executed once a day. The report is sent only when there is something to report. Each report requires you to set the variable @EmailRecipients to the e-mail accounts of those who are to receive the report. If you specify multiple addresses, use a semicolon to separate them.

• Report: SQL Server - SSRS Reports that Failed 

  Job: DBA-SQL Overview - Report SSRS - Report FailuresDescription: Report any SSRS Reports that encountered errors since the previous execution of the daily SQL Overview package.

• Report: SQL Server - SSRS Report Subscriptions that Failed

  Job: DBA-SQL Overview - Report SSRS - Report Subscription FailuresDescription:  Report any SSRS Report Subscriptions that encountered errors since the previous execution of the daily SQL Overview package.

• Report: SQL Server - SQL Overview - SSRS Subscriptions Table Refresh Errors

  Job: DBA-SQL Overview - Report Table Refresh Errors-Reporting_SubscriptionsDescription: Report errors encountered during the previous 25 hours by the new subscriptions package. After the report is sent, any errors older than 25 hours are deleted from the table SSIS_Errors_Reporting_Subscriptions.

Customization

• To execute the subscription failure report after each time the subscription collection package is completed, add start report job step to the "DBA-SQL Overview - Check - Report Service Subscriptions".
• To change the number of days a row is kept in the table dbo.ReportServices_SubscriptionsLastStatus update the package container "Prune Subscription LastStatus" with the desired value. Current setting 45 days.
• To change the number of days a row is kept in the table dbo.ReportServices_Notifications update the package container "PruneNotifications" with the desired value. Current setting 45 days.

Conclusion:

The principal purpose of SQL Overview is to provide an automated review of all SQL Server instances each day by emailing reports of potential problems. With this update to SQL Overview, you will now be kept informed of any SSRS report failures. This allows you to be proactive in addressing report issues often before the recipient's notice a report is missing. Sometimes you will discover a report or subscription that is no longer needed and can be removed.

Attached to this article is the zipped file with all the SQL Overview packages, tables, jobs, reports, full installation instructions, and change history. The SSIS packages have been migrated to SQL Server 2008. In addition, there have been numerous enhancements to the package and reports to address various issues and add other features. All of the components for this article and my next article SQL Overview History are included in the attachment.

Additional Resources

• SQL Overview Part I, II, III,  IV, V, VI
• Report Server Execution Log - Microsoft
• Monitoring and Troubleshooting Subscriptions by Dean Kalanquin-MSFT 13 Jan 2009 12:03 AM
• Reporting Services Cause and Resolution of Reporting Services Errors  - Microsoft
• SQL SERVER – 2005 – 2008 – Delete Duplicate Rows by Pinal Dave - SQLAuthority.com
• SSRS Report History – keep more than 60 days by  Steve Schneider