Earlier today one of our users reported a problem trying to access an MS SharePoint hosted Microsoft SQL Server Reporting Services site.
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.
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 – Reporting Services Logs
Here is the contents of our latest RS Log File:
Problem Identification – Image
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 Identification – System – Performance – Task Manager – Physical Memory
Let us take a quick look at MS Windows – Task Manager – Physical Memory Utilization
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:
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
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.
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE ; GO EXEC sp_configure 'max worker threads', [max-number-of-worker-threads] ; GO RECONFIGURE; GO
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
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.
From our quick read, it appears that we are experiencing memory issues.
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
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 – Related Problems
- SSRS Failed to create HTTP Runtime
- Error while performing memory shrink: System.OutOfMemoryException
References – Remediation – Choices
- Configure the max worker threads Server Configuration Option