SQL Server Reporting Services Subscriptions - Stuck with Pending Status

There was a problem with a SQL Server Reporting Server: new subscriptions were not working and the Status on the report subscription would show as Pending after it started.

Deleting and recreating the subscription did not fix the issue. Reviewing the reporting service configuration did not show any bad or missing configurations. We looked deeper and found the fix to this issue. Looking back, it seems this is a simple fix but to help prevent someone else from spending a lot of time in troubleshooting we decided to write this post.

The problem ended up being there were phantom subscriptions. In other words, the database showed subscriptions that the report server did not display. The fix to this issue was to remove any of these phantom subscriptions. Here is one way how to do that.

First you find any phantom subscriptions. How you do this can be tedious but one way would be to check the reporting service log file. You may see an entry that shows something similar to the following:

ERROR: Throwing Microsoft.ReportingServices.Library.InvalidSubscriptionException: , Microsoft.ReportingServices.Library.InvalidSubscriptionException: An invalid subscription '3B7DD5D4-77E3-422D-B145-7AD2FA051C4D' was found. Subscription must be deleted and recreated.

The log showed the SubscriptionID and that is key information. Another way is to manually check if all the subscriptions in the database are showing in the report server. One query (tested with 2008R2 and 2014) that can display all the subscription information is as follows but keep in mind that will show the database side of things and that may not match what is showing on the reporting site:

  SELECT s.[SubscriptionID] -- Subscription ID
      ,s.[OwnerID] -- Report Owner
      ,s.[Report_OID] -- Report ID
      , c.Path -- Report Path
      ,rs.ScheduleID as SQLJobName -- Name of Job on SQL Server
      ,s.[Description] -- Description of the report subscription
      ,s.[LastStatus] -- Status of last subscription execution.
      ,s.[EventType] -- Subscription type
      ,s.[LastRunTime] -- Last time subscription executed
      ,s.[Parameters] -- Parameters used for subscription
      ,s.[DeliveryExtension] -- How to deliver the subscription
  FROM [ReportServer].[dbo].[Subscriptions] as s
  left join dbo.Catalog as c on c.ItemID = s.Report_OID
  left join dbo.ReportSchedule as rs on rs.ReportID = s.Report_OID
  order by c.Path

The results will show a lot of important information but the most useful will be the SubscriptionID and the Path. The SubscriptionID is obviously the ID of the Subscription and the Path shows the path to the report on the report server. If you are reading this post, chances are the LastStatus will show Pending. To take the manual approach would be to use the path to go to the report, navigate to the report properties, go to the subscriptions, and see if it matches what the database results show. If there is no match on the report server, this SubscriptionID should get captured to delete it later on but before you delete it, you may want to copy all of the results somewhere in case you need to recreate any subscriptions.

The reason the SubscriptionID is so useful is because that is what will get used to clean up the subscription from the database. This will use one of the stored procedures in the database to remove the job from the database server and all of the information in the different tables related to that subscription. The stored procedure is dbo.DeleteSubscription in the ReportServer database. Here is one way to use the stored procedure to delete the phantom subscription:

USE [ReportServer]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[DeleteSubscription]
		@SubscriptionID = '829468DE-EB84-4930-B9BE-7B4F3B00165E' --Replace this with the Actual SubscriptionID to remove from database

SELECT	'Return Value' = @return_value

GO

Once all of the phantom subscriptions are removed from the database, assuming no other issues exist on the server, the subscriptions should begin flowing again. If the subscription existed a long time then you may get spammed from the server for all of the times the subscription was not able to complete. If you have end users who have not received their email report subscriptions because of this problem, you should warn them before starting this cleanup of the possible spam.