Reporting Services is sleeping…and won’t wake up

I experienced an issue recently where, after extended periods of inactivity, our SharePoint 2010 Reporting Services (SQL 2012 RTM) environment would essentially ‘go to sleep’ and was showing no signs of waking up. Each morning, following an overnight ‘quiet period’ my users would be on the phone complaining that their Reports weren’t functioning and the error message they received was as a generic one. Under the SharePoint hood, this is what we were witnessing in the ULS:

  • Server Reporting Services     Report Server Catalog             0000    Unexpected    Throwing Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: , Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. —> System.Runtime.InteropServices.COMException (0x800703FA): Retrieving the COM class factory for component with CLSID {BDEADF26-C265-11D0-BCED-00A0C90AB50F} failed due to the following error: 800703fa.     at Microsoft.SharePoint.Library.SPRequest..ctor()     at Microsoft.SharePoint.SPGlobal.CreateSPRequestAndSetIdentity(SPSite site, String name, Boolean bNotGlobalAdminCode, String strUrl, Boolean bNotAddToContext, Byte[] UserToken, String userName, Boolean bIgnoreTokenTimeout, Boolean bAsAnonymous)     at Microsoft.SharePoint.SPRequestManager.GetContextRequest(SPRequestAuthenticationMod…    0dedb9f0-be02-4297-b334-2594a9681274

    As a workaround while this was investigated, I was using Windows Task scheduler to action a restart of SSRS Service Application at regular intervals overnight:

    Stop-SPServiceInstance -Identity d8f38b2d-9588-4e83-8031-8a6ed33559ed -Confirm:$false

    Start-Sleep -s 180

    Start-SPServiceInstance -Identity d8f38b2d-9588-4e83-8031-8a6ed33559ed

    The SSRS layer had recently been upgraded from SQL 2008R2 to SQL 2012 and thus, the application was now being leveraged as a SharePoint Service Application. And one key change was the fact that the SSRS Application was now running within its own IIS App Pool under a dedicated domain service account. And it was a setting in IIS which was proving to be the root of the problems.

    The Application Pool(s) running SSRS need to have the ‘Load User Profile’ setting set to True

    IIS will not load the domain user profile, although a user profile has to be created to store temp data in either the profile directory or registry hive. By default, if you were using ‘Network Service’, this would always be available as it was created by the system, but in this instance we’re using a domain service account. Only the OOTB standard IIS pools (Default and Classic .Net have user profiles on disk but of course, nothing is generated when you manually create an AppPool. And this is where the ‘Load User Profile’ attribute comes into play and should be set to ‘True’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s