How do you find an SSRS subscription’s SQL Agent job?

SQL Server Reporting Services (SSRS) uses SQL Server Agent to schedule report subscriptions but the name of the job doesn’t match the subscription, so when you get an error message about a failed subscription you’ll need to run a query to find it.

The Subscription table in the ReportServer database joins, via the SubscriptionId, to the ReportSchedule table and the ScheduleId of a given subscription is the name used to create the SQL Server Agent job. The below query gives us the job_id of the job that is associated with a specific subscription:

select j.job_id
from ReportServer.dbo.Subscriptions s
join ReportSchedule r on r.SubscriptionID = s.SubscriptionID
join msdb.dbo.sysjobs j on j.name = convert(sysname, r.ScheduleID)
where s.SubscriptionID = 'ab35126f-a605-4069-973b-160a59bf36c6';

From there, you can then query msdb for the execution history of the job:

select *
from msdb.dbo.sysjobhistory
where job_id = '4DA64BC9-EEBD-462B-86E3-5781AA6648AE';