Reports failing with rsReportServerDatabaseError.

Question

SQLbie on Tue, 11 Dec 2012 12:40:32


Hi,

Some of my Report subscriptions are failing with rsReportServerDatabaseError and this happens randomly. I have observed that these reports fail after ~120secs.

select *,DATEDIFF(SS,TimeStart,TimeEnd) SecondsToComplete from ReportserverDB.dbo.ExecutionLog3
where TimeStart  between '2012-12-10 12:00:00' and '2012-12-12 20:00:00'
and Status = 'rsReportServerDatabaseError'
order by TimeStart

Error:"Failure sending mail: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database"

Environment: Sharepoint 2010 Integrated SQL Server 2008 R2 Reporting Services. Reportservices DBs on SQl 2008 R2. 2 Report servers in the Sharepoint environment.

Not sure which timeout  values do I need to concentrate on. Thought the DatabaseQueryTimeout would be best option but not sure where to set that on a Sharepoint integrated Reporting services.

Should the DatabaseQueryTimeout be added to the rsreportserver.config file in  "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting" folder on each of the Report servers??

Are there any other timeout settings that need to be looked into? I dont think the error has anything to do with the web.config->httpruntime ->executiontimeout because the adhoc report executions are successful yet the subscriptions are failing randomly.

Happy to get Any other troubleshooting suggestions. Have checked the ULS logs on the Reportservers and cant find much from them. They just say the exact same error as the subscription does. 

Many thanks in advance, Sasi





Replies

Harsh Kumar on Tue, 11 Dec 2012 16:42:11


Hi,

There is similar thread, you have look at this one -> http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/b80d7f29-b271-4845-b7be-df5a9b591ed2

SQLbie on Tue, 11 Dec 2012 22:51:52


Hi Harsh, 

Thanks for the reply. Have looked into that forum and had a look into the ULS logs. Cant find much info in there as well. 

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.;

and 

Failure writing file  : Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> 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..

Dont see any errors on the DB server holding the Report server DBs. 

Thinking of finding the current DatabaseQueryTimeout value and modifying it. Not sure how to do that as the SSRS 2012 is Sharepoint 2010 integrated. Extending this value might work as a quick workaround so I can concentrate on optimizing the Processing and Rendering times. Currently the ExecutionLog3 table shows 0 for TimeDataRetrieval, Processing and Rendering for all the reports that fail.

Regards, Sasi.

David Roseneder on Tue, 11 Dec 2012 23:42:45


I also have this problem using SQL 2012 integrated with Sharepoint 2010 SP 1. The fairures appear to be random as the subscriptions succeeds some days and fails other days. Today 6 of 21 subscriptions failed. Opening the report in sharepoint / report viewer works fine and subscription will then run ok. Reportserver log and execution log are not providing any clues as to why it fails. Does anyone know of additional logging that can be turned on in the sharepoint / webservices to delve deeper.

Example Report: PRODUCTION_ORDERS_Schedule_PACMIN

The reportserver log (verbose) shows

w3wp!library!11!12/12/2012-05:40:22:: i INFO: Call to CleanBatch() ends
w3wp!schedule!14!12/12/2012-05:45:08:: i INFO: Handling Event TimedSubscription with data 5c2a2de0-ef80-43b7-a7a3-7e92ca6b05a4.
w3wp!library!14!12/12/2012-05:45:08:: i INFO: Schedule e853a205-c731-4cfa-ab79-3e616f03b796 executed at 12/12/2012 05:45:08.
w3wp!schedule!14!12/12/2012-05:45:08:: i INFO: Creating Time based subscription notification for subscription: 5c2a2de0-ef80-43b7-a7a3-7e92ca6b05a4
w3wp!library!14!12/12/2012-05:45:08:: i INFO: Schedule e853a205-c731-4cfa-ab79-3e616f03b796 execution completed at 12/12/2012 05:45:08.
w3wp!library!10!12/12/2012-05:45:08:: i INFO: Initializing EnableExecutionLogging to 'True'  as specified in Server system properties.
w3wp!notification!10!12/12/2012-05:45:09:: i INFO: Handling subscription 5c2a2de0-ef80-43b7-a7a3-7e92ca6b05a4 to report PRODUCTION_ORDERS_Schedule_PACMIN.rdl, owner: CQMSRAZER\droseneder, delivery extension: Report Server Email.
w3wp!library!10!12/12/2012-05:45:09:: i INFO: RenderForNewSession('http://intranet.cqmsrazer.com/Shared Reports/Supply_Team_Reports/State_Of_Play/PRODUCTION_ORDERS_Schedule_PACMIN.rdl')
w3wp!alertingruntime!12!12/12/2012-05:45:22:: i INFO: Executed AlertingEventQueueWorker.CleanInactiveRows()
w3wp!runningjobs!14!12/12/2012-05:46:38:: i INFO: Adding: 1 running jobs to the database
w3wp!library!10!12/12/2012-05:47:09:: 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.;
w3wp!library!10!12/12/2012-05:47:09:: i INFO: Initializing EnableExecutionLogging to 'True'  as specified in Server system properties.
w3wp!emailextension!10!12/12/2012-05:47:09:: e ERROR: Error sending email. Exception: Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> 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.

Execution log3: shows

InstanceName ItemPath UserName ExecutionId RequestType Format Parameters ItemAction TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount AdditionalInfo
SP2010\@Sharepoint /{09026984-f3de-413c-88d3-595b9a2d0454}/Shared Reports/Supply_Team_Reports/State_Of_Play/PRODUCTION_ORDERS_Schedule_PACMIN.rdl CQMSRAZER\SP_Service ab4m0k45kyn35crlg1eqjjb1 Subscription EXCELOPENXML NULL Render 2012-12-12 05:45:09.170 2012-12-12 05:47:09.307 0 0 0 Live rsReportServerDatabaseError 0 0 NULL
SP2010\@Sharepoint /{09026984-f3de-413c-88d3-595b9a2d0454}/Shared Reports/Supply_Team_Reports/State_Of_Play/PRODUCTION_ORDERS_Schedule_PACMIN.rdl CQMSRAZER\SP_Service txbgwzavnzfb0455v4akqdub Subscription EXCELOPENXML NULL Render 2012-12-11 09:03:03.460 2012-12-11 09:03:10.357 5246 24 1522 Live rsSuccess 26162 175 <AdditionalInfo><ProcessingEngine>2</ProcessingEngine><ScalabilityTime><Pagination>0</Pagination><Rendering>0</Rendering><Processing>0</Processing></ScalabilityTime><EstimatedMemoryUsageKB><Pagination>21</Pagination><Rendering>431</Rendering><Processing>238</Processing></EstimatedMemoryUsageKB><DataExtension><SQL>1</SQL></DataExtension><Connections><Connection><ConnectionOpenTime>1</ConnectionOpenTime><DataSource><Name>DWH_Reporting</Name><DataSourceReference>DWH_Reporting</DataSourceReference><DataExtension>SQL</DataExtension></DataSource><DataSets><DataSet><Name>ProdSchedData</Name><RowsRead>175</RowsRead><TotalTimeDataRetrieval>5245</TotalTimeDataRetrieval><QueryPrepareAndExecutionTime>3405</QueryPrepareAndExecutionTime><ExecuteReaderTime>3404</ExecuteReaderTime><DataReaderMappingTime>0</DataReaderMappingTime><DisposeDataReaderTime>0</DisposeDataReaderTime></DataSet></DataSets></Connection></Connections></AdditionalInfo>

SQLbie on Wed, 12 Dec 2012 19:06:14


Hi David,

In fact, we have the same issues when the SSRS has been upgraded to 2012 with Sharepoint 2010 + SP1. Also, one of the changes during the upgrade is the Reportserver Databases have been moved to the same server that holds Sharepoint DBs. 

I have an inkling to raise the DatabaseQueryTimeout - which is for Native Mode. Since its Sharepoint Integrated, am looking for a proper resource that points to the exact steps to increase this parameter. 


David Roseneder on Thu, 13 Dec 2012 00:02:25


Hi Sasi

Our installatiion is totally new from scratch. No upgrades. Sharepoint was installed with sp1 install slipstreamed.

Reports were migrated into a Sharepoint library from a SQL 2008 server (not r2). New data sources were created in the library and the reports updated via report builder. Subscriptions were manually added.

I have created entirely new reports from scratch and subscriptions still randomly fail for them. So I think we can rule out any Upgrade Issues.

All the subscriptions are owned by me and run between 5.30 and 8.00am. I have a sql job set up to rerun failed subscriptions at 8.30 by starting the sql job pertaining to the subscription. Often they fail again. However, if I go in and run any report then run it again, they all work.

Today, I have altered the Report Processing Time-out in sharepoint from 1800 to 3600 and restarted the Report service.

Central Administration -> General Application Settings -> Report Processing Time-out ->Limit reporting processing timeout (in seconds)

Central Administration -> System Settings -> Manage services on Server

Note that the rsreportserver.config in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\Reporting did not change.

After the restart, I noted the following entries in the reportserverservicelog.

Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
Initializing Timeout to '10' second(s) as specified in Configuration file
Initializing DatabaseQueryTimeout to '120' second(s) from SQL Reporting Service Application.
Initializing ConnectionTimeout to '15' second(s) from SQL Reporting Service Application

Will see what happens tomorrow.

David

SQLbie on Thu, 13 Dec 2012 16:48:27


Hi David,

Thanks for those pointers.I havent done any changes yet on my end. But I am following the steps as

  1.       need to change the DatabaseQueryTimeout for the Reporting services in Sharepoint Integrated mode.   
  2.       Need to clear any unwanted subscriptions on the Reports and the SQL Jobs.
  3.       Look for options to increase the <httpRuntime executionTimeout="3600" /> on the reporting services web.config file.
  4.       Apply the SP1 for SSRS 2012 on both Report servers. May need Testing SP1 on Dev servers.

I am curious about the results of your changes. Please keep updated.

Also, I was curious whether the Processing timeout is similar to the DatabaseQueryTimeout. I guess the processing of a report has much to do than the Database querying. Was going through http://msdn.microsoft.com/en-us/library/ms157405(v=sql.90).aspx to get a better idea on that. 


David Roseneder on Thu, 13 Dec 2012 23:49:31


Hi Sasi

Still got the problem, 10 of 21 subscriptions failed. Ran a report to the screen and then reran the subscription jobs and they all worked.

Steps today are

1. httpRuntime executionTimeout

added executionTimeout to ...\Reporting\web.config Line now reads

<httpRuntime executionTimeout = "10800" maxRequestLength="51200" />

2. ASP Script timeout

increased this from 00:01:30 to 00:05:00

IIS Manger -> server -> sites -> Sharepoint Web Services. find the reporting service by right click -> Manage Application -> Advanced Settings -> Physical Path. Then open ASP in right pane, expand Limits properties, Script Time-Out click apply (top right pane)

3. Changed processing options on individual reports

5.45am Report - processing timeout changed to 3600 (was previously default value)

5.53am Report - processing timeout changed to never (was previously default value)

4. Stopped and Restarted IIS

Will see what happens when the subscriptions run on Monday.

David

David Roseneder on Mon, 17 Dec 2012 02:01:30


Mon 17/12/12 6 / 36 subscriptions fail. All were rerun at 8.30am without failure.

Both 5.45 and 5.53 subscrtions that had individual processing timeouts failed.

New steps today

1. Scheduled spwakeup to run at 4.30am

2. Reportserver service - web.config

added closeTimeout="00:05:00" openTimeout="00:05:00" to binding options (defauts are 1 min)

3. Reportserver Service - ReportServer.config

although this is supposedly ignored when running in sharepoint mode (default is 120 secs)

   <Add Key="DatabaseQueryTimeout" Value = "600" />

changed from 5 to 10

<Add Key="MaxScheduleWait" Value="10" />

SQLbie on Mon, 17 Dec 2012 09:19:43


Hi David,

Thanks for the update. On my end - working towards reproducing the errors in the DEV environment.

This might take a bit of time but will update results here also in case of any findings.

David Roseneder on Mon, 17 Dec 2012 23:22:26


Tue 18/12/12 11/13 report fail. Users also having trouble running reports from sharepoint. Rerunning subscriptions also failed.

Monitored memory. The 12G is dived up as sql sharepoint 3 to 4 G, sql dbs 2 to 4G.

Some paging was occuring. Available bites was done to 190M.

Restarted SQL Sharepoint and IIS and ran spwakeup on all sites. Availble memory is 2.6G. Rerunning subscriptions worked.

Will monitor memory later today and tomorrow.

David Roseneder on Fri, 21 Dec 2012 00:16:45


Extra memory arrived today and will be installed next week.

Altered processing of one of the first reports run to be based on snapshots created 15 mins earlier. Result was fewer failures.

Wiil let you know how it all goes when I get back from xmas break on 7 Jan.

David Roseneder on Wed, 20 Feb 2013 22:41:29


Increased memory form 12 to 28G and fixed the amount sql used. Paging rarely happens now and 2-3G is always available.

Made little difference. Reports are still randomly failing.

Rescheduled reports to run at different times and moved reports that always ran to problem times. Mixed results but still unrelaible.

Rechecked SPNs / Kerberos. Nothing unusual.

Rescheduled reports away from problem times. No failures for several weeks then many failures 2 days in a row then good again then bad again.

Have no idea what the issue is.

Current plan is to deploy new virtual SQL 2012 and sharepoint 2013 servers and shutdown existing server. SQL Reporting services will not be installed / used.

David Roseneder on Fri, 12 Apr 2013 02:50:15


Continued intermitent failures. Disapointed that there is no solution to this problem. Will be bringing forward the Sharepoint 2013 upgrade and moving reports to Excel. With some luck there will be no need for SSRS. If there is, it will be installed seperately and not integrated with Sharepoint.

David Roseneder on Thu, 18 Apr 2013 00:32:49


Sharepoint 2010 Server crashed with config issues. Moved reports back to natiive SSRS instance. Massive improvement in speed. and reliablility.