I got a problem when I try to identify SQL Agent job that runs a Reporting Services subscription. However, I found there are a few Jobs owned by SSRS cannot match any Subscription. For instance, I have 16 jobs in job agent, but I only could identify 13 of them.
Does anyone have any ideas about this situation? Is there any way to figure it out where the unexpected job come from and trace them?
Appreciate it!!
It takes a bit of footwork, but you can figure this all out by looking in the ReportServer database that you specified at install time or in the SSRS Configuration tool.
The key tables you want to look at is reportSchedule and Subscriptions. Both will create jobs in your SQL Server Agent. The ScheduleID should match the job name. You can match ReportID with ItemID in the Catalog table to get the name of the report.
Here a query you can run to get more info on subscriptions. I made this into a report in SSRS that I review daily. Note: I probably ripped this off from another StackOverflow answer.
select c.Name,s.LastRunTime,s.LastStatus,s.Description,s.ScheduleID
from ReportServer.dbo.Subscriptions as s
left join ReportServer.dbo.Catalog as c
on c.ItemID=s.Report_OID order by LastRunTime desc