March 22, 2016 at 7:43 pm
I am getting the error in the topic returned when running a report against table(s) that has about 7 million rows. It's not an entirely complicated query that is used by the report and the report will mostly return one row based on the parameter provided to the report.
I see more instances of this error in the log file. Attaching the full log file to the topic for reference.
Please let me know if anybody has run into this and was able to resolve it.
The resources for SSRS troubleshooting is depressingly limited on MSDN and technet.
Few things I tried:
Increase the space allocated to the ReportServerTempDb database to ensure it has more than %GB of space most of the time.
Increase the report query time-out
Increase verbosity of the log file but didn't get any further information.
Below is the full log/error from the D:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles\ folder
Contents from the LogFile showing the error followed by the execution of the report pasted here as well.
library!WindowsService_52!a80!03/22/2016-22:39:06:: i INFO: Call to CleanBatch()
library!WindowsService_52!a80!03/22/2016-22:39: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, 0 edit sessions.
library!WindowsService_52!a80!03/22/2016-22:39:07:: i INFO: Call to CleanBatch() ends
library!ReportServer_0-104!1f3c!03/22/2016-22:46:46:: i INFO: Call to GetPermissionsAction(/XCenter/Loss Prevention/Find Customer ID(s)).
library!ReportServer_0-104!a98!03/22/2016-22:46:46:: i INFO: Call to GetSystemPropertiesAction().
library!ReportServer_0-104!214c!03/22/2016-22:46:46:: i INFO: Call to GetPropertiesAction(/XCenter/Loss Prevention/Find Customer ID(s), PathBased).
library!ReportServer_0-104!19dc!03/22/2016-22:46:46:: i INFO: Call to GetSystemPermissionsAction().
library!ReportServer_0-104!2bd8!03/22/2016-22:46:46:: i INFO: Call to GetSystemPropertiesAction().
library!ReportServer_0-104!19dc!03/22/2016-22:46:47:: i INFO: Call to GetPermissionsAction(/XCenter/Loss Prevention/Find Customer ID(s)).
library!ReportServer_0-104!a98!03/22/2016-22:46:47:: i INFO: Call to GetSystemPropertiesAction().
library!ReportServer_0-104!214c!03/22/2016-22:46:47:: i INFO: Call to GetPropertiesAction(/XCenter/Loss Prevention/Find Customer ID(s), PathBased).
library!ReportServer_0-104!19dc!03/22/2016-22:46:47:: i INFO: Call to GetSystemPermissionsAction().
library!ReportServer_0-104!cf0!03/22/2016-22:46:47:: i INFO: Call to GetSystemPropertiesAction().
library!ReportServer_0-104!1f3c!03/22/2016-22:46:47:: i INFO: RenderForNewSession('/XCenter/Loss Prevention/Find Customer ID(s)')
library!WindowsService_52!b48!03/22/2016-22:49:06:: i INFO: Call to CleanBatch()
library!ReportServer_0-104!5f8!03/22/2016-22:49:54:: i INFO: Call to GetPermissionsAction(/XCenter/Loss Prevention/Find Customer ID(s)).
library!ReportServer_0-104!214c!03/22/2016-22:49:54:: i INFO: Call to GetSystemPropertiesAction().
library!ReportServer_0-104!214c!03/22/2016-22:49:54:: i INFO: Call to GetPropertiesAction(/XCenter/Loss Prevention/Find Customer ID(s), PathBased).
library!ReportServer_0-104!19dc!03/22/2016-22:49:54:: i INFO: Call to GetSystemPermissionsAction().
library!WindowsService_52!b48!03/22/2016-22:56:06:: 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.;
dbcleanup!WindowsService_52!b48!03/22/2016-22:56:07:: e ERROR: Error in CleanOrphanedSnapshots: 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.
library!WindowsService_52!b48!03/22/2016-22:56:07:: 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, 0 edit sessions.
library!WindowsService_52!b48!03/22/2016-22:56:07:: i INFO: Call to CleanBatch() ends
March 22, 2016 at 9:05 pm
An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database
I love the precision in these errors. Why don't they just say, "an error occurred, we don't know why."
Anyhow,Is SSRS and SQL Server running on the same box or sharing any resources? Is the SSRS box and the SQL Box on the same network? Is the ReportServer DB on the same instance as the Database that you're querying? A troubleshooting strategy will be way different based on the answer to these questions.
Some general thoughts:
In this type of situation I like to run a trace, capture the query that's getting sent to the database, then run it in SSMS to make sure it's running okay. If it's not then perhaps that's the problem, if it runs really quick then the problem is in SSRS or somewhere between the DB and SSRS.
Look at the servers when running the report. Do either of them (assuming there's more than one) spike the CPU to 100% when running the report? Does the event viewer talk mention any disk issues. On the SQL box where the source DB and SSRS DB reside do you have sp_blitz? I have found that helpful for troubleshooting these kinds of things.
Maybe run SP_WHO2 or sp_whoisactive on the SQL box(es) while the report is running. Any blocking, lock escalation, etc? Have you checked for deadlocking when the report runs...
-- Itzik Ben-Gan 2001
March 23, 2016 at 8:47 pm
Thanks Alan. B for the response.
Here are the answers to your questions with additional notes/observations from today.
Anyhow,Is SSRS and SQL Server running on the same box or sharing any resources?
Yes and No
So SSRS is on our BIBox where we have SQL Server Database engine running to host the ETL jobs, however, this report is querying SQL Server instance as it data source.
So even though SSRS is sharing resources with the SQL Server Databases that is used for other BI purposes, the SQL data source it is querying is on different box.
Is the SSRS box and the SQL Box on the same network?
Yes
Is the ReportServer DB on the same instance as the Database that you're querying?
No
A troubleshooting strategy will be way different based on the answer to these questions.
Some general thoughts:
In this type of situation I like to run a trace, capture the query that's getting sent to the database, then run it in SSMS to make sure it's running okay.
If it's not then perhaps that's the problem, if it runs really quick then the problem is in SSRS or somewhere between the DB and SSRS.
Yes, I forgot to mention it in my initial post but I capture the query via a trace ran it from SSMS and it returned in less than a second.
Look at the servers when running the report. Do either of them (assuming there's more than one) spike the CPU to 100% when running the report?
I checked both the Reporting Server and the SQL Server that is queried by the report and did not see abnormal CPU spike. In fact it was well below 25%
Does the event viewer talk mention any disk issues?
No, checked on this before posting the question but forgot to mention.
On the SQL box where the source DB and SSRS DB reside do you have sp_blitz? I have found that helpful for troubleshooting these kinds of things.
What is sp_blitz? Can you elaborate?
Additional info from today’s testing.
So it seems the issue is intermittent. When I can this morning, I tried running the report searching for different parameter values and it report returned results in less than 2 seconds. I got a consistent throughput for at least an hour and after that the error again reappeared, where the report kept running only to result in the error after 15-20 mins.
It looks some issue on the SSRS server/database as I saw the same error messages both in the Prod and Test Reporting Server instances but I cannot seem to find out what that could be.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply