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)
