SQL Reporting Services doesn’t do a very good job keeping the SQL Agent clean by removing expired or otherwise unusable subscriptions from the job list. To deal with this, we created a script that pulls some details about these old subscriptions, including the report responsible, the last run time and status, and the user who originally scheduled it. If you notice your SQL Agent job list getting excessively long, you can use this query to identify the culprit reports and owners, and then either notify them or remove the old subscriptions manually yourself (run this on the server with your SSRS databases):
select c.Name as ReportName, s.EventType, s.Description as SubscriptionDescription, s.LastStatus as LastSubscriptionStatus, s.LastRunTime SubscriptionLastRunTime, case when recurrencetype = 1 then 'One Time' when recurrencetype = 2 then 'Hourly' when recurrencetype = 4 then 'Daily' when recurrencetype = 5 then 'Monthly' when recurrencetype = 6 then 'Month Week' else 'Other' end as RecurranceType, s.DeliveryExtension, u.UserName as SubscriptionSetUpBy, s.ModifiedDate as SubscriptionLastModifiedDate from [ReportServer].[dbo].[Subscriptions] s join [ReportServer].[dbo].[Catalog] c on c.ItemID = s.Report_OID join [ReportServer].[dbo].[Users] u on u.UserID = s.OwnerID join [ReportServer].[dbo].[reportschedule] rs on c.itemid = rs.reportid and s.subscriptionid = rs.subscriptionid join [ReportServer].[dbo].[schedule] sch on rs.scheduleid = sch.scheduleid where s.EventType &amp;lt;&amp;gt; 'RefreshCache' and s.LastRunTime &amp;lt; dateadd(m, -3, getdate()) order by c.name
There are a number of similar scripts out there that pull much of this information together, but there wasn’t one that collected all the details we were looking for in one place. From here, you can deal with the subscriptions as you see fit.
Note that you can just remove the old subscriptions by brute force if you’d prefer, and SSRS will clean up the orphaned SQL jobs, but I’ve preferred to review the list and notify users as we’ve never had too much volume to deal with. If you want to just delete them straight away, you can do so here:DELETE ReportServer.dbo.Subscriptions WHERE InactiveFlags != 0 OR LastRunTime &lt; dateadd(m, -3, getdate())
2 thoughts on “Removing expired/unused SSRS subscriptions”
query threw error on
“where s.EventType <> ‘RefreshCache’
and s.LastRunTime < dateadd(m, -3, getdate()”, specifically <>
Please correct it.
I’ve updated this post – it looks like code had the < and > replaced when I pasted it at some point. Thanks!