Microsoft – SQL Server – Reporting Services – Error – “e ERROR: Failed to create HttpRuntime” & “e ERROR: Failed to get appdomain” & “e ERROR: Error state. Internal abort for pipeline”

Background

Earlier today one of our users reported a problem trying to access an MS SharePoint hosted Microsoft SQL Server Reporting Services site.

Texual:

The error message read "The attempt to connect to the report server failed.  Check 
your connection information and that the report server is a compatible version.  For more information about this error navigate to the report server on the local 
server machine, or enable remote errors.

Image:

HTTP-HTML Error

We had the same problem a few weeks ago, and thankfully then we pushed for the Microsoft Reporting Services Error Logs Folder to be exposed as a Windows SMB Network Share.

Problem Identification

Problem Identification – Reporting Services Logs

Here is the contents of our latest RS Log File:

Problem Identification – Image

ReportServerServices_ErrorDetail

Problem Identification – Textual

rshost – Errors:

rshost!rshost!63c!02/13/2014-10:08:56:: e ERROR: Failed to create HttpRuntime 80131530.

rshost!rshost!63c!02/13/2014-10:08:56:: e ERROR: Failed to get appdomain 80131530, 
pipeline=0x00000000007AFC70.

rshost!rshost!63c!02/13/2014-10:08:56:: e ERROR: Error state. Internal abort for 
pipeline=0x00000000007AFC70 ...

appdomainmanager – Errors:

appdomainmanager!DefaultDomain!404!02/13/2014-10:12:53:: e ERROR: Error while performing memory shrink: System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
appdomainmanager!DefaultDomain!404!02/13/2014-10:12:56:: e ERROR: Error while performing memory shrink: System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.

Problem Identification – System – Event Viewer

Here is the relevant portion of MS Windows Event Viewer.


Log Name:      Application
Source:        ASP.NET 2.0.50727.0
Date:          2/13/2014 10:08:56 AM
Event ID:      1334
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      SQLLAB
Description:
Failed to initialize the AppDomain:ReportServer_MSSQLSERVER_0

Exception: System.SystemException

Message: Failed to create AppDomain.

StackTrace:    at System.Web.Hosting.ApplicationManager.CreateAppDomainWithHostingEnvironment(String appId, IApplicationHost appHost, HostingEnvironmentParameters hostingParameters)
   at System.Web.Hosting.ApplicationManager.CreateAppDomainWithHostingEnvironmentAndReportErrors(String appId, IApplicationHost appHost, HostingEnvironmentParameters hostingParameters)

InnerException: System.OutOfMemoryException

Message: Exception of type 'System.OutOfMemoryException' was thrown.

StackTrace:    at System.AppDomain.nCreateDomain(String friendlyName, AppDomainSetup setup, Evidence providedSecurityInfo, Evidence creatorsSecurityInfo, IntPtr parentSecurityDescriptor)
   at System.AppDomainManager.CreateDomainHelper(String friendlyName, 
Evidence securityInfo, AppDomainSetup appDomainInfo)
   at Microsoft.ReportingServices.AppDomainManager.RsAppDomainManager.CreateDomain(String appDomainName, Evidence securityInfo, AppDomainSetup appDomainInfo)
   at System.AppDomain.CreateDomain(String friendlyName, Evidence securityInfo, AppDomainSetup info)
   at System.Web.Hosting.ApplicationManager.CreateAppDomainWithHostingEnvironment(String appId, IApplicationHost appHost, HostingEnvironmentParameters hostingParameters)
Event Xml:

Problem Diagnosis

Problem Identification – System – Performance – Task Manager – Physical Memory

Let us take a quick look at MS Windows – Task Manager – Physical Memory Utilization

WindowsTaskManager-Memory-Overall

Out of 8 GB of RAM, only 53 MB is available.

Problem Identification – System – Performance – Task Manager – Processes

Steps that we will take to determine which processes are using the most memory:

  • Launch Task Manager
  • Access the “Processes” Tab
  • Ensure that the “Show processes from all users” check-box is checked
  • Access the “Memory” column and switch to ordering by “Memory” in descending order

Here is a “process by process” resource consumption:

Windows-TaskManager-Processes

Problem Diagnosis – System Threads

Run the query pasted below to review system threads and try to determine whether additional threads have been spawned.

Configure the max worker threads Server Configuration Option
http://msdn.microsoft.com/en-us/library/ms190219.aspx

SELECT
		s.session_id,
		r.command,
		r.status,
		r.wait_type,
		r.scheduler_id,
		w.worker_address,
		w.is_preemptive,
		w.state,
		t.task_state,
		t.session_id,
		t.exec_context_id,
		t.request_id

FROM sys.dm_exec_sessions AS s

		INNER JOIN sys.dm_exec_requests AS r

		    ON s.session_id = r.session_id

		INNER JOIN sys.dm_os_tasks AS t

			ON r.task_address = t.task_address

		INNER JOIN sys.dm_os_workers AS w

			ON t.worker_address = w.worker_address

WHERE s.is_user_process = 0;

If it seems that we are having problems with too many threads, I will suggest that we place a hard-limit on the number of threads that will be started by the SQL Engine.

Syntax:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'max worker threads', [max-number-of-worker-threads] ;
GO
RECONFIGURE;
GO


Example:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'max worker threads', 900 ;
GO
RECONFIGURE;
GO

Please keep in mind that the scenario of too many worker threads is highly unlikely.  And, the number of threads will have to over above 300 or so for an extended period of time.

Problem Diagnosis – SQL Server – Processes Resource Consumption

Review running sql server processes and gauge resource usage:

select 
		  tblSysProcess.spid
		, tblSysProcess.lastwaittype
		, [databaseName]
            = db_name(tblSysProcess.dbid)
		, tblSysProcess.physical_io
		, tblSysProcess.memusage
		, tblSysProcess.cmd
		, tblSQLText.text as [sqlText]
		, objectName
            = object_name
            (
                tblSQLText.objectid
                , tblSQLText.dbid
            ) 

from   master.dbo.sysprocesses tblSysProcess

	  inner join sys.dm_exec_query_stats AS tblQueryStat

		on tblSysProcess.sql_handle = tblQueryStat.sql_handle

	  CROSS APPLY sys.dm_exec_sql_text(tblQueryStat.sql_handle) AS tblSQLText

order by
	      tblSysProcess.memusage desc
	    , tblSysProcess.physical_io desc


Image:

SQLProcessesUsingMemory

When we ran the query above the biggest memory consumers are MS SQL Server Engine (msdb), SharePoint (WSS_Content), and Reporting Services (ReportServerSP).

Problem Diagnosis – Read Microsoft SQL Server Error Log

Let us do a quick read of SQL Server’s Error Log.

Query Command
Read all of error log exec master.dbo.xp_readerrorlog 0,1
Read all of error log – filter for errors exec master.dbo.xp_readerrorlog 0,1, ‘errors’
Read all of error log – filter for memory exec master.dbo.xp_readerrorlog 0,1, ‘memory’

Image:

errorLog-FilteredForMemory

From our quick read, it appears that we are experiencing memory issues.

Remediation

I think we have two things we need to do:

  • Set max memory uptake for the SQL Server Engine
  • Restart SQL Server Reporting Services

Remediation – SQL Server Engine – Cap Memory usage

Analysis

On an 8 GB Box, pretty much dedicated to the MS SQL Server Engine and Reporting Services, we can adjudicate memory allocation as follows:

  • OS and Device Drivers – 2 GB
  • Backup / Virus Detection – 0.5 GB (500 MB)

And, so we end up with max SQL Server Memory of 8 GB – 2.5 GB or 5.5 GB

Set Max Memory

-- Turn on advanced options
EXEC  master.dbo.sp_configure 'Show Advanced Options',1;
GO
RECONFIGURE;
GO

-- Set max server memory = 5500MB for the server
EXEC  master.dbo.sp_configure 'max server memory (MB)',5500;
GO

RECONFIGURE;
GO

Remediation – Restart Microsoft SQL Server Reporting Services

Once we cap SQL Server Memory, the SQL Server Engine will start releasing memory back to the Windows OS.

And, we should restart MS SQL Server Reporting Services to clear existing errors for that application.

References

References – Related Problems

References – Remediation – Choices

Leave a Reply

Please log in using one of these methods to post your comment:

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