ReportServerTempDB growing large : One of the causes

  • Article

Have you ran across the problem of ReportServerTempDB size keeps growing and it never comes down? Though there are more than one reason to it, today we’ll see one of the reason and how to isolate the issue.

On one fine day you discover that the ReportServerTempDB is growing and not reducing in size. The immediate next thing you have to do is, open the Reporting services log files located in <Installation Drive>\<Program Files or Program Files(x86)>\Microsoft SQL Server\<SSRS Instance>\Reporting Services\LogFiles.

This is just to understand what is exactly happening within Reporting Services.

Again, this is just one of the many causes for ReportServerTempDB growing. Ensure your scenario exactly matches the below discussion before you go ahead.

Once you’re within it, open the most recent log file and most recent – 1 log file, search for the keyword: “Call to CleanBatch”. If you don’t find it in at least one of the log files then most probably here is the culprit.

By default, Reporting service runs the background clean up task for every 10 minutes to ensure all the expired sessions are cleaned up from ReportServerTempDB. This can be modified by the setting “CleanupCycleMinutes” in the RsReportServer.config file located in <Installation Drive>\<Program Files or Program Files(x86)>\Microsoft SQL Server\<SSRS Instance>\Reporting Services\ReportServer.

We recommend not to change this unless you’re very sure about modifying this setting as it is optimized for most of the scenarios.

Coming back to our conversation, If no traces of “Call to CleanBatch” means, for some reason the clean up cycle is not executing as expected.

To further isolate the issue, scan through the log files starting from the most recent one till you locate something similar to: “library!WindowsService_xxxxxxxxxx:: e ERROR: ServiceStartThread: Exception caught while starting service”.

This clearly means there is a failure on the background processing thread while reporting service was starting up.

In our case, the above message was followed by an exception:

library!WindowsService_xxxxxxxxxxxxxxx:: e ERROR: ServiceStartThread: Exception caught while starting service. Error: 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: The SELECT permission was denied on the object 'databases', database 'xxxxxxxxxxxxxxx', schema 'sys'.
The SELECT permission was denied on the object 'databases', database 'xxxxxxxxxxxxxxx', schema 'sys'.

library!WindowsService_11!7f00!11/21/2013-01:55:28:: e ERROR: ServiceStartThread: Attempting to start service again...

This clearly points us to a conclusion that the Reporting service windows service account didn’t have necessary permission on one of the system databases.

To ensure this is taken care off, bring up Reporting services configuration manager and click on the Database tab.

Click on the Change Credentials button and follow the onscreen instructions. Ensure to connect to the database using a SQL Admin account. Make sure this process is completed without any error.

Apply the Database settings and this entire process would have granted the required permission for the Report Server service account on all the required database objects.

Restart the report server Windows service from the configuration manager itself.

Verify the log file to ensure the Cleanup cycle is resumed as shown below.

library!WindowsService_6!1054!11/29/2013-00:50:26:: i INFO: Call to CleanBatch() ends
library!WindowsService_6!1280!11/29/2013-01:00:25:: i INFO: Call to CleanBatch()
library!WindowsService_6!1280!11/29/2013-01:00:25:: i INFO: Cleaned 0 batch records, x policies, x sessions, x cache entries, x snapshots, x chunks, x running jobs, x persisted streams, x segments, x segment mappings, x edit sessions.
library!WindowsService_6!1280!11/29/2013-01:00:25:: i INFO: Call to CleanBatch() ends

And the above sequence would be repeated for every 10 minutes, by default.

One thing for sure, never change any of the reporting service related configuration or Database related settings, permissions outside the Reporting service configuration manager as it could easily lead you to unnecessary failures and errors.

Happy Reporting!

Selva.

[All the posts are AS-IS and without any warranty]