Report Server uses Report Server Temporary database to store session and execution data, information related to cached reports. These tables in the ReporServerTemp database hold temporary data and there are certain Stored Procedures which periodically remove older data from the ReportServerTemp database.
Therefore, ReportServerTemp database only being a scratchpad holding temporary data for a limited period of time it is recommended that we have the Recovery Model for the ReportServerTemp database set to Simple. By default, the Recovery Model for ReportServerTemp database is set to Simple.
When we configure Reporting Services to work with Always On Availability Groups, the Recovery Model for the ReportServerTemp database changes to Full. As a result of which there are scenarios where the ReportServerTemp database keeps growing in size as with the Recovery Model set to ‘Full’, SQL Server will keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated. This can also result in sessions getting blocked which can in turn cause performance issues on SSRS as creating a connection to Report Server Database might fail.
Therefore, it is always recommended and a best practice to remove ReportServerTemp database from Always on AvailabilIty groups set up and have the Recovery Model set to ‘Simple’. Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions. Since the space in the transaction log can be reused, the transaction log will not grow forever as in the case of "Full" recovery model. As mentioned earlier, that the ReportServer database only stores Temporary data, removing it from Always On set Up will not cause any impact on Reporting Services.
Author: Kane Conway – Escalation Engineer, SQL Server BI team, Microsoft @kaneco
