The Problem
We recently migrated the underlying “ReportServer” and “ReportServerTempDB” databases of our SQL Server Reporting Services (SSRS) environment to a newer instance. Everything worked fine and there were no issues for a couple of days. However, when members of our analytics team went to deploy updates to a report, they encountered a strange error that looked something like this:
------ Deploy started: Project: SSRS Project: Debug ------ Deploying to https://ssrs.domain.com/reportserver Deploying report '/folder/report'. Error rsReportServerDatabaseError : An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. Deploy complete -- 1 errors, 0 warnings ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========
The Research
As we began to look into this error, it quickly became apparent that it is a very generic one that covers a variety of situations. Some examples I encountered include:
- Missing RSExecRole role, or permissions missing from it
- Space issues with the database files or its storage
- Communication problems either to the Availability Group or between its replicas
The team and I worked through various scenarios and none seemed to really fit our situation. Finally, one intrepid analyst had a breakthrough.
The Resolution
The analytics team had been using Visual Studio to deploy the reports when they encountered the error above. With SSRS Report Builder, my friend was able to see a more detailed error.
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: Length of LOB data (148068) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit, other that the limit imposed by the data type.
The statement has been terminated.Eureka! It all made sense now. I have been bitten by this exact situation before in various forms, and it always manifests in the weirdest of ways. To be specific, this is what’s going on:
- You have a SQL Server database that is using either replication or CDC.
- The “Max Text Replication Size” setting of the instance is set to the default value of 65,536 bytes.
- Something tries to insert a large amount of data that exceeds this length (in our case saving the updated report).
- SQL Server does not allow the data to be inserted and throws the detailed error above.
- Depending on the program you’re using, you may see the full detailed error or the truncated one.
With that information in hand, the resolution was simple. You can update the value to a larger-than-default value or -1 (unlimited) using sp_configure, as the error states (code below). You can also simply right-click the instance in SSMS, go to Properties, navigate to the Advanced tab, and change the setting under Miscellaneous. The setting takes effect immediately, with no service restart required.
I hope this helps you narrow down similar odd errors quickly! The code from the sp_configure link is below:
USE master;
GO
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXECUTE sp_configure 'max text repl size', -1;
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
Leave a Reply