20 January 2022

How do you identify and re-run failed SQL Server Reporting Services (SSRS) subscriptions from SQL Server?

How do you identify and re-run failed SSRS subscriptions from SQL Server?

If you’re ever in a situation where you have failed SQL Server Reporting Services (SSRS) subscriptions like we had when we were Managing 20,000 SSRS Subscriptions you might find it useful to have a query which identifies the jobs associated with failed subscriptions. 

The SQL Agent jobs run successfully but if you look in the Subscriptions table, you’ll see that the LastRunTime column for the subscription is not updated which gives you the opportunity to search for subscriptions whose LastRunTime is smaller in value than the LastRunTime for the corresponding SQL Agent job (i.e. the job has run more recently than the Subscription has).

The following query, identifies the subscriptions that haven’t run when expected, as well as providing the sp_start_job command needed to re-run those subscriptions:

select j.name as sql_agent_job_name, su.SubscriptionID, su.LastRunTime as subscription_last_run_time,
last_executed_step_date as job_last_run_time,command_to_rerun = 'exec msdb.dbo.sp_start_job ''' + j.name + ''''
from msdb..sysjobs j
join (select job_id, max(session_id) session_id, max(last_executed_step_date) as last_executed_step_date  from msdb.dbo.sysjobactivity group by job_id) as i on j.job_id = i.job_id
join ReportServer..ReportSchedule rs on rs.ScheduleID = j.name
join ReportServer..Subscriptions su on su.SubscriptionID = rs.SubscriptionID
where (LastRunTime is null or LastRunTime < last_executed_step_date) --only looking for subscriptions that have never executed or that haven't executed since the agent job last
and InactiveFlags < 128 -- ignore disabled subscriptions
and j.category_id = 100 -- ReportServer category
and last_executed_step_date is not null -- ignore jobs that haven't run

This also serves as a good basis for a query that allows you to monitor for and alert when subscriptions haven’t run when they were scheduled to run.

If you’re interested, here are the descriptions for the various InactiveFlags:

  • Active = 0,
  • DeliveryProviderRemoved = 1,
  • SharedDataSourceRemoved = 2,
  • MissingParameterValue = 4,
  • InvalidParameterValue = 8,
  • UnknownItemParameter = 16,
  • MissingExtensionEncryptedSettings = 32,
  • CachingNotEnabledOnItem = 64,
  • DisabledByUser = 128

You’ll note that, in the above query, we’re using sysjobactivity instead of sysjobhistory to get the most recent run time. After a SQL Agent job runs, sysjobhistory is not immediately updated to reflect the most recent run time, which is why it’s more accurate to use sysjobactivity. Aaron Bertrand offers a detailed explanation of why this is, here:  How does SQL Agent update next_run_time values? (stackexchange.com)


Let’s Talk

Have a SQL challenge? We can help.

"*" indicates required fields

This field is for validation purposes and should be left unchanged.