Monitoring and Troubleshooting Subscriptions
As the owner of several production reports, I have subscriptions that fire on a daily or weekly basis to remind people to close their bugs or verify their labruns. As usual, the password for the account I use in the report datasources will expire, and I invariably forget to update all of them and I'll get the "I've not received the report for days" email. As the owner of the Report Server, I also don't want my box to waste time and resources processing a bunch of invalid reports or subscriptions.
For these reasons, I want to monitor the Report Server and know what is not working in the reports and subscriptions.
Let me start by giving a high level view of how subscriptions are defined and processed, but first:
Disclaimer: we do not officially support querying all of the Reporting Services tables mentioned below, the columns and information in them may change in the future.
Note: This information refers to the SQL 2008 catalog, this info will generally also apply to SQL 2005, but I've not verified it there yet. Also, unless otherwise specified, the tables mentioned below are in your Report Server catalog database with whatever name you gave it, and yes, you need permissions necessary to query them.
Ok, back to the fun stuff.
When you create a subscription several things are added to the RS server:
- A row is placed in the Subscriptions table identifying the name of the report, along with parameter settings, data driven query info and so on to process the subscription
- A row is placed in the Schedule and ReportSchedule tables with the timing of the subscription
- A SQL Server Agent job is created to control the scheduled execution of the report, and this is stored in the sysjobs and sysjobsteps of the MSDB database. The agent job name is a guid value that represents the ScheduleId (yes, in case you've not yet noticed, this makes your Agent Job list messy with a bunch of guids that make it hard to work with your non-subscription jobs, we are hearing you:).
This query shows how the subscription information is stored in the various related tables, along with the most useful information you will need to diagnose issues later:
select
'SubnDesc'
= s.Description,
'SubnOwner'
= us.UserName,
'LastStatus'
= s.LastStatus,
'LastRun'
= s.LastRunTime,
'ReportPath'
= c.Path,
'ReportModifiedBy'
= uc.UserName,
'ScheduleId'
= rs.ScheduleId,
'SubscriptionId'
= s.SubscriptionID
from
ReportServer.dbo.Subscriptions s
join
ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
join
ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
join
ReportServer.dbo.Users uc on uc.UserID = c.ModifiedByID
join
ReportServer.dbo.Users us on us.UserID = s.OwnerId
join
msdb.dbo.sysjobs j on j.name = CONVERT(nvarchar(128),rs.ScheduleId)
I use a similar query in a report with parameters to filter to certain reports, subscriptions, owners, and last_status values.
When the subscription runs several things happen:
- The SQL Server Agent job fires and puts a row in the Event table in the RS catalog with the settings necessary to process the subscription
- The RS server service has a limited number of threads (2 per CPU) that poll the Event table every few seconds looking for subscriptions to process
- When it finds an event, it puts a row in the Notifications table and starts processing the subscription, which includes executing the data driven subscription query (if present), processing the report, rendering it to the specified format (HTML, PDF, Excel etc), and delivering the final result to either an email address or a file share. Note that there will also be a row in the ExecutionLog table for the report execution.
This query shows the subscription processing information that you can use to diagnose problems:
select
'Report'
= c.Path,
'Subscription'
= s.Description,
'SubscriptionOwner'
= uo.UserName,
'SubscriptionModBy'
= um.UserName,
'SubscriptionModDate'
= s.ModifiedDate,
'ProcessStart'
= dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessStart),
'NotificationEntered'
= dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.NotificationEntered),
'ProcessAfter'
= dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessAfter),
n
.Attempt,
'SubscriptionLastRunTime'
= dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.SubscriptionLastRunTime),
n
.IsDataDriven,
'ProcessHeartbeat'
= dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessHeartbeat),
n
.Version,
n
.SubscriptionID
from
Notifications n
join
Subscriptions s on n.SubscriptionID = s.SubscriptionID
join
Catalog c on c.ItemID = n.ReportID
join
Users uo on uo.UserID = s.OwnerID
join
Users um on um.UserID = s.ModifiedByID
Common problems with subscription delivery:
If your subscription emails are not being delivered to Inboxes across the company, there are several different things that can cause this:
- The SQL Server Agent job is not firing
This could be because the service is not running due to a password expiration or some other cause, and RS does not see anything to process. In this case, get the ScheduleId guid from the above query, find the job with that guid as its name, and check the job history to make sure the job fired. You will also see that the Event and Notifications tables are empty.
- RS is not processing the subscription events
The main issue here is that the RS service event processing threads are not processing the events. The main cause here is that the event processing threads are all tied up, and not processing the events as fast as they arrive. You will see that the agent jobs are firing, but the Events and Notifications tables have a large number of entries. The number of threads is fixed, you cannot increase it, so you need to figure out what they are processing and why they are not firing or keeping up. You can get the subscription and report information, and run the report or the data driven query manually to see if they are healthy. One thing I noticed is that a person had scheduled a report execution snapshot to fire every half hour, but the report took over an hour to process, so at any given time, several instances of the event were running at the same time occupying all of the threads, and nothing else was getting executed.
You may also notice that everything is healthy, but there are common hotspot times in the day (for example, 8am or midnight) when a lot of subscriptions are set to fire at the same time, and they may take a long time to complete, so the subscription processing cannot keep up and falls behind. In this case, you may encourage people to run them at different times if possible, or you could consider adding a second reporting server configured as a two node NLB cluster with the other. In this case, both servers will be polling the events table in the RS catalog and processing the events in parallel.
- SMTP is not delivering the emails
This could be because the email address is bad, or there are problems with the SMTP configuration. I won't go into SMTP here, I'm a database guy, but RS needs it to be working to deliver the report. The Event and Notifications tables will be empty (RS has done its part), the LastStatus column of the Subscriptions table and the subscription status in Report Manager will show an error similar to "Failure sending mail: The user or group name 'domain\username' is not recognized. Mail will not be resent."
- The report or subscription is invalid
Again, this can usually be tested by running the report manually, but there are a myriad of causes that will also show in the subscription status. For example, the datasource may be invalid due to an expired password or an account for a person who has left the company, a subreport may have been removed or renamed, a server, database, or table used in the query may have been removed or renamed and so on. Statistically, this is the source of over 90+% of failed subscriptions I've seen.
So in summary, you can write reports based on the information in these catalog tables to monitor subscriptions, and take action before you get the "I've not received reports for days" emails, and encourage the report and subscription owners to keep their stuff clean and healthy and be good reporting citizens.
In case its not obvious, you may want to host these monitoring reports on a different server because if subscriptions are not firing, you may not get the subscription based monitoring report to tell you that subscriptions are broken!
I hope this helps, please let me know!
Thanks, Dean
As usual, "This blog is provided 'AS IS' with no warranties, and confers no rights."
Comments
Anonymous
January 23, 2009
Recently I have had little time to devote to blogging, but I did want to pass along a couple of referencesAnonymous
March 13, 2009
Hey There – I've not blogged in a while, and as a Reporting Services admin I’ve recently taken an interestAnonymous
March 25, 2010
Hi. Is there a way to transfer existing subscriptions from one report server to another. ThanksAnonymous
January 27, 2011
Very useful queries. Thanks for sharing! Looking forward to more meaningful job names :)Anonymous
February 04, 2011
Hi Thanks for the detailed write up. We run data driven subscriptions delivering 1000s of files. The understanding I get from this post will help us to debug any problems... Regards Jegan(http://EasyBI.WordPress.com)Anonymous
March 05, 2014
Simple explanation and very helpful. Please continue topics on SSRS.Anonymous
July 06, 2014
Hi Dean Thanks for the article. It saved me lots of time. I had an issue with one of my users using the User!UserID expression in a report that ran under a subscription. When the report is run by a subscription, the report is run unattended, and therefore there is no User for the expression to evaluate. The report runs fine, when you run it manually (under your own UserId), but fails when run by a subscription. The symptom of this error is the Status column in the ExecutionLogStorage table is set to 'rsHasUserProfileDependencies'. Thanks - Adam (http://www.dimodelo.com/blog)