This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
ReportServerTempDB in AlwaysOn Availability group
Question
ReportserverTempdb logfile is 6TB in size and growing and I need help. I am supporting a huge reporting server farm and the client wants reporting services to be in AlawysON Availability group(AAG) and we configured reportserver and reportserverTempDB on in three node Primary replica, synchronize Secondary replica and DR in a different location with manual synchronization.
Microsoft recommends reportservertempdb to be in simple recovery mode but if you add it to AAG that is not an option. Does anyone has reportservertempdb in AAG?
I would like to advise customer to take reporservertempDB out of AAG and we can just keep the copy of the reportservertempdb on each node and that should be fine.
Please advise.
asked 2016-04-11 by user42148
Answer
Microsoft describes it’s support for Availability Groups with SSRS in BOL. In particular is the section titled “Report Server Databases and Availability Groups.” In my experience, having ReportServer and ReportServerTempDb be part of an AG works fine in most cases, but there is some peculiar behavior with subscriptions and failover–which is described in the BOL article.
Having independent ReportServerTempDb databases on each server, but not participating in the AG is not something I would try–but it would likely be problematic in it’s own ways. It would also not be a supported configuration.
You should look at this answer to see why your transaction log is growing. I suspect it is due to an open transaction in the ReportServerTempDb. On a number of occasions, I have seen the CleanBatch process get hung, and create a scenario where it has an open transaction but is performing no work. The net result is that your transaction log gets huge. If you see the same behavior, then simply kill the spid with the open transaction, and things should return to normal.
answered 2016-04-11 by Andy Mallon