I have a box that I use to play around with Subscriptions and Alerting in SQL Server Reporting Services 2012 in SharePoint mode, recently the alert that I’m use to get at mornings didn’t show up for some days , so I started to investigate what happened, usually you can troubleshoot this kind of issues following some of the recommendations from Dean’s blog in Monitoring and Troubleshooting Subscriptions , but in this case something else was happening.

In the basics Alerting works similar to Subscriptions, when you create an Alert with a Schedule a SQL Server Agent Job is created , when the time of the Alert execution arrives, the SQL Server Agent Job calls a Store Procedure that insert a row in the Alerting “Event” table, the Reporting Services Background processing polls continuously the “Event” table and when it founds a row there start the processing of the Alert

When I looked in the Reporting Services logs in the SharePoint mode in \Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\LogFiles , I found that the Reporting Services background processing was never started, when it start it write some entries to the log like

w3wp!dbpolling!3!04/23/2012-08:43:05:: i INFO: EventPolling polling service started
w3wp!runningjobs!3!04/23/2012-08:43:05:: i INFO: EventPolling HeartbeatTimer timer enabled: Next Event: 0 seconds.  Cycle: 300 seconds
w3wp!dbpolling!3!04/23/2012-08:43:05:: i INFO: AlertSchedulePolling polling service started
w3wp!dbpolling!3!04/23/2012-08:43:05:: i INFO: NotificationPolling polling service started

In that moment I remembered that during my testing of Alerting that every time that the services is restarted (with IISReset for example) it takes a while to start the background processing in SharePoint mode because it is triggered initially by a SharePoint timer job.

I checked the status of my Job In Central Admin and the last time it run was a while ago.

You can use SharePoint Central Administration to check your timer jobs , the following screenshots shows the pages, Starting with the Monitoring Section in Central Admin

image

Now this is the Status and History of the Timer Jobs, Reporting Services Job is highlighted

image

And here are the details of the Reporting Services Timer Job

image

I made a little of SharePoint research and I found that there is a Windows Service called “SharePoint 2010 Timer” which was stopped (due some account password changes), I started the service and in few minutes I see the entries in the log showing that the Background processing started (also started to get my Alerts)

You can also check the status of the Reporting Services Timer Job with PowerShell using the SharePoint 2010 Management Shell

PS C:\>$myTimer=Get-SPTimerJob |where-object {$_.Name -like '*reporting*'}

Now you have the timer in a variable you can start it from the command line

PS C:\>$myTimer | Start-SpTimerJob

òr view the details

PS C:\>$myTimer | Format-List

Description                 : This timer job triggers the background processing operations for the SQL Server Reporting Services Service Application it is associated with. This timer job is also associated with the following web applications - SharePoint - 80
DisplayName                 : SQL Server Reporting Services Service Job for Reporting Web Service
EnableBackup                : True
Service                     : ReportingWebService
WebApplication              :
Server                      :
LockType                    : None
Schedule                    : every 5 minutes between 0 and 59
Title                       : SQL Server Reporting Services Service Job for Rep
                              orting Web Service
LastRunTime                 : 5/7/2012 1:40:29 PM
Retry                       : False
IsDisabled                  : False
VerboseTracingEnabled       : False
HistoryEntries              : {, , , ...}
DiskSizeRequired            : 0
CanSelectForBackup          : False
CanRenameOnRestore          : False
CanSelectForRestore         : False
Name                        : Reporting Web Service
TypeName                    : Microsoft.ReportingServices.SharePoint.SharedServ
                              ice.Service.DoWorkTimerJob
Id                          : 275ab87a-40ac-4313-9b9c-5d958008da05
Status                      : Online
Parent                      : ReportingWebService
Version                     : 28013
Properties                  : {}
Farm                        : SPFarm Name=spdb
UpgradedPersistedProperties : {}