September 8, 2017 at 8:07 am
Hi All,
I am getting this error whenever I try to run a report through report server. It appears to be happening like clockwork every morning, and I am struggling to find the cause. You can still navigate to the report server, click on a report, set parameters etc. However when the data tries to load in I get that error where the data should be at. When this happens all reports are effected with the same error.
Things I have tried:
Restarting just SSRS service
Restarting All SQL services
Crying at my desk in acceptance of my total lack of knowledge of SSRS
Calling my mother weeping with sorrow
None of these have worked so far, as a last resort I reboot the entire machine that SQL resides on and it will come back up just fine.
I need help...please.
If you tell me where to get more information about the issue I can do that. I have unfettered access to the entiriety of the server, so just let me know what you need to further the analysis.
Thanks,
Ben
September 8, 2017 at 8:19 am
How long does the it take for this message to appear once you open the report; is it quite quick, or after a little while? Is SSRS running on the same server as the data engine? Can you see the SSRS account logging into the SQL Server in the logs to run the reports at the time you are trying (you may need to set the server to log successful (and failed) attempts, as by default it only logs failed)? Are you able to also log into the Server's data engine while you are experiencing this issue?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 8, 2017 at 10:13 am
It takes some time for the message to appear (definitely reminiscient of a default timeout window). SSRS is running on the same server as the data engine, unsure where to find the SQL server logs. I can log into the data engine using management studio and it works nominally.
The entire database actually stays in operation and works great, it's just SSRS has issues pulling the data for some reason.
September 8, 2017 at 12:49 pm
ben.cox 63023 - Friday, September 8, 2017 10:13 AMIt takes some time for the message to appear (definitely reminiscient of a default timeout window). SSRS is running on the same server as the data engine, unsure where to find the SQL server logs. I can log into the data engine using management studio and it works nominally.The entire database actually stays in operation and works great, it's just SSRS has issues pulling the data for some reason.
Ok, I was wondering if the issue was a connection problem, but if both are on the same instance, then this is highly unlikely.
You say that this only seems to happen at certain times; Is the server under any additional load at that time? How long does the query take to run in SSMS, and when the report does run how long does it take? It could be that the report is taking longer than normal to render bceause the server is under more pressure than normal, thus less resources are allocated to the query; thus the maximum timeout period of the report is reached.
If this seems to be the issue, you can change the timeout period for report rendering. There are a couple of ways you can do this, one is in the report itself, on the Processing Options page in the Web Portal. Alternatively,you can change the value in the web.config file (by default, located in C:\Program Files\Microsoft SQL Server\MSRS[SQL Version Number].[Instance Name]\Reporting Services\ReportServer (replace the value in brackets with the relevant details for your instance), and then amend the value for httpRunTime executionTimeout.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 8, 2017 at 1:34 pm
I do believe increasing the timeout would allow it to run, but it's not feasible for the users and doesn't really solve what the underlying cause is which is where I'm attempting to get to. I found an additional message in the logs that said that our reportserver db was reporting an error to SSRS. This was different than the message that we actually see when trying to run the report.
I can't say that the SP would run in SSMS, however about 20 other vb apps that access that same data on that same server run perfectly fine during this issue.
However those vb apps don't access the reportserver db, while SSRS does.
September 8, 2017 at 2:33 pm
Is anything posted in the SSRS logs during the times when you get the error?
You can find them here:
%ProgramFiles%\Microsoft SQL Server\MSSQL.x\Reporting Services\LogFiles
Joie Andrew
"Since 1982"
September 8, 2017 at 3:02 pm
library!ReportServer_0-2!5100!09/08/2017-08:11:44:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: , Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.RunningJobsDb.AddRunningJobs(Hashtable runningJobs)
--- End of inner exception stack trace ---;
I saw this error many times repeated over in the log file.
September 8, 2017 at 3:12 pm
How long do the datasets in the query take to run if you execute them outside of the report, like from SSMS or SQLCMD?
Joie Andrew
"Since 1982"
September 8, 2017 at 3:26 pm
I can't answer that for when the problem is there. However during everything working ok, it's about 4-5 seconds to generate the report on report manager website and little over a quarter of a second when running the SP from SSMS.
When we experience the issue again I can definitely try it while the issue is existing.
September 8, 2017 at 3:42 pm
Do the errors always happen during the same times/days? You mentioned earlier in the thread restarting all SQL services, does that mean that SSRS and the database engine are on the same machine? Are any SQL jobs going on during the times of the error causing blocking which could be triggering the timeout to occur? Is the box a VM? If so are any VM operations like VM snapshots occurring during the times of the errors?
Joie Andrew
"Since 1982"
September 11, 2017 at 7:51 am
They always start when the users get it. Yes SSRS and the database engine are on the same machine. Unsure how to check what SQL jobs would be running during that time frame. Could you direct me to perhaps a log for SQL? It's not a virtual machine.
September 12, 2017 at 10:01 am
We may have found the issue:
Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script:A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory. ' A job step received an error at line 2 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\MLNS-002$a).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find drive. A drive with the name 'SQLSERVER' does not exist. 'A job step received an error at line 2 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\MLNS-002$a).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'You cannot call a method on a null-valued expression. '. Process Exit Code -1. The step failed.
This is the error we are getting on a job that runs at 2 AM. Well no users get in until 6 am so it would make sense that we are only seeing this as the users come in for the day. Restarting the server fixes it I would guess because it cancels that job. The server has a very low I/O on the disks (we are fixing this issue soon) so when we've tested between the hours of 5-6 AM we've had success. However once all the users hit it it's probably overloading it since it's still trying to run that job.
We looked and the powershell for SQL is not there for some reason, we had an issue with a developer back when this started. He had uninstalled powershell (unintentionally), so crossing my fingers that we can get the powershell SQL module back up and running and won't see this issue anymore!
September 12, 2017 at 5:19 pm
Since restarting all the services never corrects the problem and it only stops when you restart the entire server, I would be more inclined to suspect something on the server itself, not necessarily any of the SQL services. And the issue is that you are getting a timeout error connecting with this error:
The report server cannot open a connection to the report server database.
So the Reporting Services can't connect to the database on the SQL Server instance - the ReportServer and ReportServerTempdb are the report server databases. I'd go back and look at some of what Joie posted and look if any operations at all like that (snapshots) are happening, go through the Windows event for the times when this happens, see if you have other errors in SQL Server or SQL Agent when this happens by checking the SQL Server error logs and the SQL Agent error logs.
Sue
February 7, 2024 at 9:53 am
Did we get the solution for this issue. I am also getting same problem since SSRS reports got hung and self resolved after 20-25 mins.
Our report server is running in separate VM and it pulls the data from databases (database server) in other VM. Following is the log which I found from log file. I have sleepless nights with this issue. Please respond if you found any solution.
ERROR: Transaction commit failed. Exception thrown: 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: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) ---> System.ComponentModel.Win32Exception: No process is on the other end of the pipe
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply