SSRS 2008 crashng when running a report with huge amount of data
We have a huge amount data that is loaded (using flat files, upto 80GB in size) from the mainframe on a daily basis after which our users use SSRS to view the results using predeployed reports. One of the report query brings back a huge amount of data but
when rendering the reports, it dies and restarts the service.
library!ReportServer_0-12!1310!10/14/2010-17:49:45:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout
or low disk condition within the database., ;
session!ReportServer_0-12!1310!10/14/2010-17:49:45:: e ERROR: Sql Error in GetSnapshotData: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection
failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
ui!ReportManager_0-11!1b34!10/14/2010-17:49:45:: Unhandled exception: Microsoft.Reporting.WebForms.ReportServerException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within
the database. (rsReportServerDatabaseError) ---> Microsoft.Reporting.WebForms.ReportServerException: For more information about this error navigate to the report server on the local server machine, or enable remote errors
--- End of inner exception stack trace ---
at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.SetExecutionId(String executionId, Boolean fullReportLoad)
at Microsoft.Reporting.WebForms.ServerReport.LoadFromUrlQuery(NameValueCollection requestParameters, Boolean fullReportLoad)
at Microsoft.Reporting.WebForms.ReportDataOperation..ctor()
at Microsoft.Reporting.WebForms.HttpHandler.GetHandler(String operationType)
at Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
library!WindowsService_5!990!10/14/2010-17:52:07:: i INFO: Call to CleanBatch()
library!WindowsService_5!990!10/14/2010-17:52:07:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 2 snapshots, 12 chunks, 0 running jobs, 0 persisted streams, 20 segments, 20 segment mappings.
library!WindowsService_5!990!10/14/2010-17:52:07:: i INFO: Call to CleanBatch() ends
library!ReportServer_0-12!1ba8!10/14/2010-17:52:55:: Call to GetPermissionsAction(/).
library!ReportServer_0-12!1bd0!10/14/2010-17:52:55:: Call to GetPropertiesAction(/, PathBased).
library!ReportServer_0-12!11d4!10/14/2010-17:52:55:: Call to GetSystemPermissionsAction().
library!ReportServer_0-12!aa4!10/14/2010-17:52:55:: Call to ListChildrenAction(/, False).
library!ReportServer_0-12!9f0!10/14/2010-17:52:55:: Call to GetSystemPropertiesAction().
processing!ReportServer_0-12!980!10/14/2010-17:52:56:: w WARN: Processing Scalability -- Memory Shrink Request Received
library!ReportServer_0-12!ea4!10/14/2010-17:53:43:: Call to GetPermissionsAction(/Comcast Reports).
library!ReportServer_0-12!ea4!10/14/2010-17:53:43:: Call to GetPropertiesAction(/Comcast Reports, PathBased).
library!ReportServer_0-12!ea4!10/14/2010-17:53:43:: Call to GetSystemPermissionsAction().
library!ReportServer_0-12!ea4!10/14/2010-17:53:43:: Call to ListChildrenAction(/Comcast Reports, False).
library!ReportServer_0-12!ea4!10/14/2010-17:53:43:: Call to GetSystemPropertiesAction().
library!ReportServer_0-12!9f0!10/14/2010-17:54:35:: Call to GetPermissionsAction(/Comcast Reports/House Passers).
library!ReportServer_0-12!e64!10/14/2010-17:54:35:: Call to GetPermissionsAction(/Comcast Reports/House Passers).
library!ReportServer_0-12!15fc!10/14/2010-17:54:35:: Call to GetPropertiesAction(/Comcast Reports/House Passers, PathBased).
library!ReportServer_0-12!1ba8!10/14/2010-17:54:35:: Call to GetPropertiesAction(/Comcast Reports/House Passers, PathBased).
library!ReportServer_0-12!aa4!10/14/2010-17:54:35:: Call to GetSystemPermissionsAction().
library!ReportServer_0-12!1bd0!10/14/2010-17:54:35:: Call to GetSystemPermissionsAction().
library!ReportServer_0-12!11d4!10/14/2010-17:54:35:: Call to ListChildrenAction(/Comcast Reports/House Passers, False).
library!ReportServer_0-12!aa4!10/14/2010-17:54:35:: Call to ListChildrenAction(/Comcast Reports/House Passers, False).
library!ReportServer_0-12!9f0!10/14/2010-17:54:35:: Call to GetSystemPropertiesAction().
library!ReportServer_0-12!18c0!10/14/2010-17:54:36:: Call to GetSystemPropertiesAction().
library!ReportServer_0-12!1014!10/14/2010-17:54:40:: Call to GetPermissionsAction(/Comcast Reports/House Passers/House Change No Change).
library!ReportServer_0-12!1014!10/14/2010-17:54:40:: Call to GetSystemPropertiesAction().
library!ReportServer_0-12!1014!10/14/2010-17:54:40:: Call to GetPropertiesAction(/Comcast Reports/House Passers/House Change No Change, PathBased).
library!ReportServer_0-12!1014!10/14/2010-17:54:40:: Call to GetSystemPermissionsAction().
library!ReportServer_0-12!1014!10/14/2010-17:54:40:: Call to GetSystemPropertiesAction().
runningjobs!WindowsService_5!990!10/14/2010-17:56:30:: w WARN: Previous request for Running Requests DBstill executing, skipping...
rpcserver!DefaultDomain!af0!10/14/2010-17:57:21:: i INFO: Managed server timed out possibly caused by slow GC. Granting additional timeout of 30 sec.
rpcserver!DefaultDomain!af0!10/14/2010-17:57:56:: e ERROR: Managed server timed out. Timeout=150 sec. Gc extension timeout=30 sec.
rpcserver!DefaultDomain!af0!10/14/2010-17:57:56:: i INFO: Generating a dump from the RPCServer ProcessMonitor.
runningjobs!ReportServer_0-12!998!10/14/2010-17:58:14:: w WARN: Previous request for Running Requests DBstill executing, skipping...
My server environment:
Database and SSRS are on two different servers. Both are SQL Server 2008 with 32GB of RAM.
October 15th, 2010 6:59pm
It seems that your flat-file + the load on the report server exceed your available hard disk space. Note that when you generate a large report the underlying data is pulled into the report server and cached in the ReportServerTempDB database. So for
short period of time you've got the data duplicated in a couple places on disk. When the underlying report server catalog database runs out of space and/or looses connection this will trigger a restart of the reportr server.
I would suggest investigating SQL resource governers or restricting the underlying database size of the database catalogs. You could also look into more hard drive space, since it seems to be the bottleneck rather than available
RAM (since RS now caches to hard disk when low RAM conditions in 2008).
-JonHP
Microsoft SQL Server Reporting Services
Free Windows Admin Tool Kit Click here and download it now
October 16th, 2010 1:56am
Thank you for the update...
So, how or when does it use the RAM when generating a report. When I monitored the server while the report was being rendered, the memory utilization for SSRS went from 2GB to using 22GB out of the available 32GB and then restarted itself. We also have 88GB
of free space on the hard disk where the ReportServerTempDB is housed. I am going to run another quick test and see if it uses up all the disk space.
Nimesh
October 20th, 2010 7:26pm
I did another run to confirm if the hard disk was actually running out of space. When I ran the report, the memory went up to 82% (about 24GB) from 2 GB and the report failed. However, I did not see the ReportServerTempDB grow at all. The ReportServerTempDB
was at 3GB initially and it stayed contstant. This time around the SSRS did not restart itself. Following is the error generated in the log file. Any help\suggestion is appreciated.
ibrary!WindowsService_4!89c!10/20/2010-23:03:07:: i INFO: Call to CleanBatch()
library!WindowsService_4!89c!10/20/2010-23:03:07:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams, 0 segments, 0 segment mappings.
library!WindowsService_4!89c!10/20/2010-23:03:07:: i INFO: Call to CleanBatch() ends
runningjobs!ReportServer_0-10!10b8!10/20/2010-23:03:10:: i INFO: Adding: 1 running jobs to the database
library!WindowsService_4!7e0!10/20/2010-23:13:07:: i INFO: Call to CleanBatch()
library!WindowsService_4!7e0!10/20/2010-23:13:09:: i INFO: Cleaned 0 batch records, 0 policies, 1 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams, 0 segments, 0 segment mappings.
library!WindowsService_4!7e0!10/20/2010-23:13:09:: i INFO: Call to CleanBatch() ends
library!ReportServer_0-10!45c!10/20/2010-23:21:20:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout
or low disk condition within the database., ;
session!ReportServer_0-10!45c!10/20/2010-23:21:20:: e ERROR: Sql Error in GetSnapshotData: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection
failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
ui!ReportManager_0-9!8d4!10/20/2010-23:21:20:: Unhandled exception: Microsoft.Reporting.WebForms.ReportServerException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within
the database. (rsReportServerDatabaseError) ---> Microsoft.Reporting.WebForms.ReportServerException: For more information about this error navigate to the report server on the local server machine, or enable remote errors
--- End of inner exception stack trace ---
at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.SetExecutionId(String executionId, Boolean fullReportLoad)
at Microsoft.Reporting.WebForms.ServerReport.LoadFromUrlQuery(NameValueCollection requestParameters, Boolean fullReportLoad)
at Microsoft.Reporting.WebForms.ReportDataOperation..ctor()
at Microsoft.Reporting.WebForms.HttpHandler.GetHandler(String operationType)
at Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
library!ReportServer_0-10!1b20!10/20/2010-23:22:34:: Using folder D:\MSSQL\MSRS10.MSSQLSERVER\Reporting Services\RSTempFiles for temporary files.
processing!ReportServer_0-10!13e8!10/20/2010-23:22:35:: w WARN: Processing Scalability -- Memory Shrink Request Received
library!WindowsService_4!b48!10/20/2010-23:23:07:: i INFO: Call to CleanBatch()
library!WindowsService_4!b48!10/20/2010-23:23:13:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 2 cache entries, 1 snapshots, 6 chunks, 0 running jobs, 0 persisted streams, 11800 segments, 11800 segment mappings.
library!WindowsService_4!b48!10/20/2010-23:23:13:: i INFO: Call to CleanBatch() ends
library!WindowsService_4!b48!10/20/2010-23:33:07:: i INFO: Call to CleanBatch()
library!WindowsService_4!b48!10/20/2010-23:33:15:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 12 cache entries, 6 snapshots, 33 chunks, 0 running jobs, 0 persisted streams, 23041 segments, 23041 segment mappings.
library!WindowsService_4!b48!10/20/2010-23:33:15:: i INFO: Call to CleanBatch() ends
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 9:15pm
You got this error because SSRS is a piece of garbage with a zillion fixes and it still doesn't work as well as an old IBM 360-30 mainframe did back in the 1970s when we could print reports that were thousands of pages long with narry a hiccup by the server
and we did it with 32MB of memory and three disks of 5.7 MB each.
Doesn't say much good about Microsoft's abaility to product quality software. In addition to this error that we also get, we ocassionally receive this wonderful error -
An error occurred during rendering of the report. ---> System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
That error totally stops SSRS from processing reports. Memory isolation has been around for years, once again the mainframe world has been doing it for many years. Microsoft needs to take a few lessons of customer service and product quality from IBM, not
only on SSRS but many of their other products.
Until that day happens others as well as me will continue to suffer due to Microsoft's lack of quality and their poor customer service.
Glad I got that off my chest.
Rich
September 13th, 2011 8:29pm
You might try increasing your DatabaseQueryTimeout in rsreportserver.config . SSRS could be timing out while waiting on the DB.
Good luck,
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 1:47pm