Sql-server – Report subscription deletion failed due to FK constraint

constraintsql serversql-server-2008-r2ssrs

We have many SSRS report timed subscriptions (SQL Server 2008R2 SP4).
One of our report developers tried to delete a report subscription, and got the following error:

An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Get Online Help The DELETE statement conflicted with the REFERENCE constraint "FK_ReportSchedule_Subscriptions". The conflict occurred in database "ReportServer", table "dbo.ReportSchedule", column 'SubscriptionID'. The statement has been terminated.

I solved the issue by manually deleting the schedule record from the ReportServer.dbo.ReportSchedule table.

The steps were the following:

1. Find the relevant subscription that needs to be deleted.

SELECT sub.SubscriptionID, 
       rs.ScheduleID,
       SUB.[Description] 
       ,SUB.EventType 
       ,SUB.DeliveryExtension 
       ,SUB.LastRunTime
FROM dbo.Subscriptions AS SUB 
INNER JOIN dbo.Users AS USR ON SUB.OwnerID = USR.UserID 
INNER JOIN dbo.ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID
                            AND SUB.SubscriptionID = RS.SubscriptionID 
WHERE SUB.Description like '%**Mail reciepient here**%'
ORDER BY sub.Description

2. Find the relevant schedule that prevents the record from being deleted.

SELECT [ScheduleID]
      ,[ReportID]
      ,[SubscriptionID]
      ,[ReportAction]
FROM [ReportServer].[dbo].[ReportSchedule]
WHERE SubscriptionID = '**GUID HERE**'

3. Backup the table

SELECT * 
  INTO [ReportServer].[dbo].[ReportScheduleBackup]
  FROM [ReportServer].[dbo].[ReportSchedule]

4. Delete the schedule

DELETE FROM [ReportServer].[dbo].[ReportSchedule]
WHERE  ScheduleID ='**GUID HERE**'

The delete command deleted the schedule AND deleted the job from SQL instance that holds the RS databases. That job triggered the timed subscription.

After that, I could delete the report timed subscription from the reports web GUI.

My questions:

Best Answer

I've been having the same problem for the last few weeks and had settled on manual deletes. I think I've cracked it today. I've compared the foreign key constraint [FK_ReportSchedule_Subscriptions] on the table Subscriptions to the same on a clean (working) install of ssrs. On my clean install the constraint is set with nocheck