Mirroring TFS – Consider Index Rebuilds When Using TLog Backups
Mirroring a TFS DT is a very common action within large environments for a variety of reasons, redundancy and failover obviously being the primary catalysts for such an implementation but there are also a variety of other reasons why this is a beneficial action.
At a current client of mine where I was setting up initial, trial mirroring for the TFS instance, it because evident that the TfsIntegration Maintenance Job was causing issues with a long completion time which eventually resulted in TFS to respond uncharacteristically slow. Now this job does two things, firstly it re-indexes the TFS databases (TFSIntegration, TfsWarehouse, TfsWarehouse, TfsWorkItemTracking and TfsVersionControl) by calling the Re-indexing:TfsIntegration stored procedure. This is done by calling exec TfsIntegration.dbo.Prc_OptimizeTfsDatabases. As well, it also removes deleted process templates by calling exec TfsIntegration.dbo.prc_deleteTemplates. The first of these is the culprit though for this issue, and if you run the stored procedures directly you will see a Stop Request being issued which will result in the re-index has not being executed.
9 times out of the ten, because Index rebuilds and Defragmentation generate very hefty transaction logs, it is because a transaction log backup job (TLog backup job) is running while the re-indexing is executed. While calling TLog may help in log file reduction, it can also cause these issues when executed simultaneously with the index rebuild.