Why aren't my subscriptions working?

I ran across an interesting issue the other day with subscriptions not firing.  I had never ran across this before, but once I was done it made sense.  It was also a good exercise of walking through how subscriptions work.  This may be obvious for some people, but I found it interesting enough to share it here.

Subscriptions had been created through Reporting Services (the issue in question was on SQL 2005 SSRS, but could have just as easily been 2008).  The status of these subscriptions were never changing. 

image

We looked at the SQL Agent Job and it had run.  So, where do we go from there?

image

Looking at the Job, we see a GUID ('E10D32F0-FEAF-428A-9441-CFC53EE3AA18').  This GUID is the ScheduleID for the Schedule table within the Catalog database (typically ReportServer).  We can link that with the Subscriptions Table through the EventData column.  This is where it pulls the data for the Subscription page which shows "New Subscription" for the Status.

image

What happens is that when the Agent Job fires, it is actually just calling the following:

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='02659b71-437c-4418-856c-5e1b3240abc0'

The AddEvent Stored Procedure adds an entry into the Event Table.  So, the next place to look would be that table to see if we made it that far. Which we did.  What is also interesting here is we see a few entries.  In the customer's case that I was troubleshooting, we saw over 100 entries.

image

The next step in the adventure is to the Notifications Table.  What happens is the ReportingServicesService will have a thread that will process the Event Table.  If it detects something in the event table, it will add an entry to the Notifications table.  This table was empty.  My thought at this point was that the Event thread was not processing.  Let's see if we find anything in the RS logs. 

Something I'll clarify here as there seems to be some confusion on the 2005 side of things in regards to the log files.  We end up generating 4 different log files.  I'll explain what they are real quick.

ReportServerService__main_<Date-Time Stamp>.log - Windows Service Startup and shutdown
ReportServerService__<Date-Time Stamp>.log - Windows Service Log
ReportServer__<Date-Time Stamp>.log - Web Service Log (aka ReportServer VDIR)
ReportServerWebApp__<Date-Time Stamp>.log - Report Manager Log (aka Reports VDIR)

Subscriptions are processed by the Windows Service, so we are going to look at the ReportServerService__ log file to see if anything was reported there.  There are no errors listed.  Everything looks like it is running fine.  And, then it caught my eye.

ReportingServicesService!library!4!2/2/2009-07:22:28:: i INFO: Initializing IsSchedulingService to 'False' as specified in Configuration file.
ReportingServicesService!library!4!2/2/2009-07:22:28:: i INFO: Initializing IsNotificationService to 'False' as specified in Configuration file.
ReportingServicesService!library!4!2/2/2009-07:22:28:: i INFO: Initializing IsEventService to 'False' as specified in Configuration file.

That is interesting. To be honest, I didn't know these settings were there but they lined up with our Event/Notifications model for subscriptions.  Lets see what Books Online has to say about these settings.

RSReportServer Configuration File
https://msdn.microsoft.com/en-us/library/ms157273(SQL.90).aspx

IsSchedulingService
Specifies whether a thread is dedicated to ensuring that schedules in the report server database match those in the SQL Server Agent database. Valid values include True (default) and False.

This setting is affected when you enable or disable Reporting Services features in the SQL Server Surface Area Configuration tool. For more information, see Starting and Stopping the Report Server Windows Service.

IsNotificationService
Specifies whether the report server dedicates a thread to polling the notification table in the report server database to determine if there are pending notifications. Valid values include True (default) and False.

This setting is affected when you enable or disable Reporting Services features in the SQL Server Surface Area Configuration tool. For more information, see Starting and Stopping the Report Server Windows Service.

IsEventService
Specifies whether service processes events in the event queue. Valid values include True (default) and False.

This setting is affected when you enable or disable Reporting Services features in the SQL Server Surface Area Configuration tool. For more information, see Starting and Stopping the Report Server Windows Service.

Well that is definately interesting.  Lets change these back to True and restart the Windows Service.  We now see expected items in the log:

ReportingServicesService!dbpolling!d!2/2/2009-08:30:32:: i INFO: NotificationPolling processing 2 more items. 2 Total items in internal queue.
ReportingServicesService!dbpolling!4!2/2/2009-08:30:32:: i INFO: NotificationPolling processing item f7a3f3df-44f8-440b-b27d-d3c06c0fee52
ReportingServicesService!dbpolling!e!2/2/2009-08:30:32:: i INFO: NotificationPolling processing item b9876b12-c4bb-4991-91f1-2ce2adecb28b

And the Subscriptions now processed.  In the customer's case, we cleared out the Event table before re-enabling subscriptions so that they didn't get flooded with emails.  At that point everything started working normally.  Not sure why it was disabled to begin with, but this maybe worth checking out if your subscriptions stop.  Also, hopefully this helps to give you ideas of what to look for if you encounter an issue.

Adam W. Saxton | Microsoft SQL Server Escalation Services