May 19, 2011 at 1:00 pm
We've noticed that some of our SSRS servers over 1,000 SQL Agent jobs that haven't run in a year or more. These seem to be associated with Schedules that have a RecurrenceType of 1. Is there any sort of SSRS functionality to clean these up by removing the Schedules, ReportSchedules and SQL Agent jobs?
June 2, 2011 at 2:02 pm
I don't know of any, but I just run this on the report server:
SELECT
C.Name
,C.Path
,U.UserName
,S.InactiveFlags
,S.LastRunTime
FROM ReportServer.dbo.Subscriptions S
INNER JOIN ReportServer.dbo.Catalog C
ON S.Report_OID = C.ItemID
INNER JOIN ReportServer.dbo.Users U
ON S.OwnerID = U.UserID
WHERE InactiveFlags != 0
OR LastRunTime < GETDATE() - 120
ORDER BY LastRunTime DESC
Then after sending a warning message to the users I run this a week or so later:
DELETE ReportServer.dbo.Subscriptions
WHERE InactiveFlags != 0
OR LastRunTime < GETDATE() - 120
This will also remove the associated jobs. You should customize it to your environment or even create a job.
Keep in mind that if the subscriptions fails you should really check them out, as it is possible that developed report functionality does not support subscriptions. Case in point is several of our reports have multi select drop downs which rely on data that sometimes is not there (I did not develop those reports!!!). When the data is not there the subscription bombs and deactivates the subscription until they are manually re-enabled.
Hope this helps.
July 8, 2011 at 8:28 am
I have modified the code slightly to look for recurrencetype = 1 rather than inactiveflag
SELECT
C.Name
,C.Path
,U.UserName
,S.LastRunTime
,sch.nextruntime
,sch.recurrencetype
FROM ReportServer.dbo.Subscriptions S
INNER JOIN ReportServer.dbo.Catalog C
ON S.Report_OID = C.ItemID
INNER JOIN ReportServer.dbo.Users U
ON S.OwnerID = U.UserID
inner join reportserver.dbo.reportschedule rs
on c.itemid = rs.reportid and s.subscriptionid = rs.subscriptionid
inner join reportserver.dbo.schedule sch
on rs.scheduleid = sch.scheduleid
WHERE recurrencetype = 1
and sch.LastRunTime < GETDATE() - 120
ORDER BY sch.LastRunTime DESC
This will give you a list of all the old one-time subscriptions
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply