List of failed SSRS subscription jobs

1. Get all the failed job related to SSRS subscription
SELECT * FROM dbo.Subscriptions WHERE LastStatus LIKE 'Failure%'

2. Get all the failed job related to email subscription

SELECT r.ScheduleID,s.LastStatus,s.Description,c.Name,c.Path,s.LastRunTime FROM dbo.Subscriptions s
INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID
INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID
WHERE s.LastStatus like 'Failure%'
and s.Description like '%e-mail%'
order by s.LastRunTime desc


3. Get all the failed job realted to file write subscription

SELECT r.ScheduleID,s.LastStatus,s.Description,c.Name,c.Path,s.LastRunTime FROM dbo.Subscriptions s
INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID
INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID
WHERE s.LastStatus like 'Failure%'
and s.Description like '%Save%'
order by s.LastRunTime desc


4. Check the execution log

select * from ExecutionLog
select * from ExecutionLog2
select * from ExecutionLog3


5. Check the log file

C:\Program Files\Microsoft SQL Server\MSRS11.JTIME\Reporting Services\LogFiles\ReportServerService__MM_DD_YYYY_hh_mm_ss.log

Running SSRS Subscription job

1. First we need get the ScheduleID from the following query by report name.

SELECT r.ScheduleID,s.LastStatus,s.LastRunTime  FROM dbo.Subscriptions s
INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID
INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID
WHERE c.name='ReportName'

example:

SELECT r.ScheduleID,s.LastStatus,s.LastRunTime  FROM dbo.Subscriptions s

INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID

INNER JOIN ReportSchedule r ON c.ItemID = r.ReportID

WHERE c.name='HSMTaskReport'

2. Then get the job id by ScheduleID (it actually job name). 

select * from msdb.dbo.sysjobs where name='ScheduleID'

example:

select * from msdb.dbo.sysjobs where name='FBC6126C-C038-41DD-B40D-1DC5EE446B39'

3. Then run the job with job id.

exec msdb..sp_start_job @job_id='JOB_ID'

example:

exec msdb..sp_start_job @job_id='3E3A3645-53B6-4577-9D17-941D4B6BFCFD'