Delete orphaned delivery schedules in ReportServer
The following query can be used against any SCData_xyz (Hosted or On-Prem) to purge any orphaned delivery schedules from the ReportServer database which are no longer in the Service Center UI.
You should always backup the ReportServer database before enabling the deletion in case any unexpected results or deletions occur.
-- USE SCData_xyz; DECLARE @Delete BIT = 0 -- CHANGE TO 1 FOR DELETION -- STEP 1: PARSE THE VAR NAME FROM THE EXISTING DB_NAME DECLARE @VarDomainName NVARCHAR(100) SET @VarDomainName = (SELECT REPLACE(DB_NAME(),'SCData_','')) -- STEP 2: LOOKUP THE VAR ID BY NAME IN SCMASTER DECLARE @VarID INT SET @VarID = ( SELECT VarID FROM SCMaster.dbo.VarDomain WHERE Name = @VarDomainName ); IF @VarID IS NULL RAISERROR ('VarID was not found',16, 1); -- STEP 2: GET SUBSCRIPTIONS FROM REPORTSERVER DB LINKED TO THIS VAR WITH VarRSSubscriptionsCTE AS ( SELECT RS.SubscriptionID, (SELECT Name FROM ReportServer.dbo.Catalog WHERE ItemID = S.Report_OID) AS [Report Name], S.Description AS [Schedule Details], S.InactiveFlags, S.ModifiedDate, S.LastRunTime, S.LastStatus FROM ReportServer.dbo.Subscriptions S JOIN reportserver.dbo.ReportSchedule RS ON RS.SubscriptionID = S.SubscriptionID WHERE S.Report_OID IN ( SELECT ItemID FROM ReportServer.dbo.Catalog WHERE Path like '%/Managed Workplace/VARs/' + convert(nvarchar(10), @VarID) + '/%' and Type = 4 ) ) -- GET ACTIVE SUBSCRIPTIONID's FROM SCDATA INCLUDING REPORT POLICIES , ActiveSubscriptionsCTE AS ( SELECT RPS.RSSubscriptionGuid, C.ComponentName AS ReportName FROM ReportPolicySubscription RPS JOIN ReportPolicyReportLink PRL ON PRL.TemplateID = RPS.TemplateID JOIN SCMaster.dbo.LpiComponent C ON C.ComponentGuid = PRL.ComponentGuid UNION SELECT RS.RSSubscriptionID AS RSSubscriptionGuid, DS.ReportName FROM RSDeliverySchedule DS JOIN RSReportSubscription RS ON DS.DeliveryScheduleID = RS.DeliveryScheduleID JOIN RSReportSubscriptionSubjectLink RSL on RSL.SubscriptionID = RS.SubscriptionID --comment out this section that filters deleted sites. this may do bad things if the schedules are still linked in SCData --LEFT JOIN -- vw_SiteNotRejected S on S.SiteID = RSL.SiteID ) -- I THINK ITS SAFE TO DELETE THESE RESULTS || OR SET InactiveFlags to 0 ?? SELECT S.SubscriptionID, (SELECT Name FROM ReportServer.dbo.Catalog WHERE ItemID = S.Report_OID) AS [Report Name], S.Description AS [Schedule Details], S.InactiveFlags, S.ModifiedDate, S.LastRunTime, S.LastStatus INTO #RsOrphanedSubscriptions FROM ReportServer.dbo.Subscriptions S -- FILTER REPORTS ONLY IN RSSubscriptions CTE WHERE S.SubscriptionID IN ( SELECT SubscriptionID FROM VarRSSubscriptionsCTE ) -- FILTER OUT REPORTS IN ActiveSubscriptions CTE AND S.SubscriptionID NOT IN ( SELECT RSSubscriptionGuid FROM ActiveSubscriptionsCTE ) SELECT * FROM #RsOrphanedSubscriptions -- STEP 3: IF DELETION IS ENABLED, DELETE THE SUBSCRIPTIONS IF @Delete = 1 BEGIN DELETE FROM ReportServer.dbo.Subscriptions WHERE SubscriptionID IN ( SELECT SubscriptionID FROM #RsOrphanedSubscriptions ) END DROP TABLE #RsOrphanedSubscriptions
It is possible in some cases that a report may still exist for a deleted site in SCData, in these cases the above query will not clean up these orphans as they still technically exist in the Service Center.
To address this scenario, use the following query to delete the orphaned entries from SCData_ before cleaning up the ReportServer
with orphanRSSubscriptions as ( select rs.RSSubscriptionID, rsl.SubscriptionID, rsl.SiteID from RSReportSubscription rs left outer join RSReportSubscriptionSubjectLink rsl on rs.SubscriptionID = rsl.SubscriptionID left outer join Site on rsl.SiteID = Site.SiteID where Site.Status = 2 ) select * from orphanRSSubscriptions --delete from RSReportSubscription where RSReportSubscription.RSSubscriptionID in (select RSSubscriptionID from orphanRSSubscriptions)