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.

• See MW-6031 10.0 SP2 MR3 - Error accessing existing delivery schedule

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)