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