1

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.

  • If I had to guess the log_reuse_wait_description in sys.databases is ACTIVE_TRANSACTION for ReportServerTempDb. The cleanup process tends to hang, and prevent log reuse. – AMtwo Apr 11 '16 at 16:58
  • I also have it in AGs - despite rhetoric to the contrary anything else (including "not backing up ReportServerTempDB because you can just rebuild it somehow") is unsupported. I think if anyone else is having problems with this, you'll need to dig further and work out what's actually going on rather than attempting something radical. – Cody Konior Aug 5 '16 at 3:09
2

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.

| improve this answer | |
  • Thanks for your response AMtwo. I checked for an open transaction and there was none. The DR node is 2 days behind but syncronizing slowly but surely. ReportServer will still be in AAG but I was referring to the reportserverTempdb which can be recreated and the contents are not important but the structure so why would it not be supported. – user42148 Apr 11 '16 at 18:05
  • Ah, if your AG synchronization is 2 days behind, that would certainly explain the large transaction log. – AMtwo Apr 11 '16 at 18:09
  • 1
    The ReportServerTempDb database contains session info, temporary data, and cached reports. For session & temporary data, users would likely experience unexpected behavior following a failover when that data was suddenly missing, or different. If any report is configured to cache reports, those cached results are stored in ReportServerTempDb, so that would be the most tangible case of unpredictable behavior when failing over. – AMtwo Apr 11 '16 at 18:17
0

I have ReportServerTempDB in SQL AG but I write the log to 'NUL':

--SET TO '0' to complete from Primary; set to '1' to complete from secondary
If sys.fn_hadr_backup_is_preferred_replica ('ReportServerTempDB') <> 1
BEGIN
    -- This is not the preferred replica
    raiserror( 'This action is not preferred on this replica',10,1)
END
    ELSE
BEGIN
BACKUP LOG ReportServerTempDB TO DISK='NUL:'
END
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.