Timeout with TFS2010 Backup/Restore Power Tool

December 3, 2010

I peviously alread blogged about the TFS2010 Backup/Restore Power Tool, but there are still some gotchas you should be aware of.

At a customer where I made use of the TFS2010 Backup/Restore Power Tool we ran into the (known) timeout issue during a TFS Backup execution.

Active backup plan configuration: full backup each week, differential backup each day, transactional backup each 30 minutes.

The timeout (600 seconds) was caused by very big transactional log files (> 15 GB) that couldn’t be stored in time to the backup location. No matter what backup plan configuration you choose, the transactional log files of all TFS databases are continuously growing because the recovery mode of the TFS databases is set to "Full". To keep it short here, the Full recovery mode is used because it provides greater protection for data than the Simple recovery model. It relies on backing up the transaction log to provide full recoverability and to prevent work loss in the broadest range of failure scenarios. More details on SQL Server recovery modes can be found here.

As a quick fix, I changed the recovery mode of the involved databases from Full to Simple and shrunk the log files. After that I switched the recovery mode back to Full. But the issue with the growing transactional log files (+ timeout) will continue to pop up in the (near) future …

So, I was thinking about setting the recovery mode of the TFS databases to Simple permanently and switching to a nightly full backup each day. I assumed that we will always be able to do a restore to one of those full backups (maximum loss of data = 1 day) … No! Just don’t do this! The Backup/Restore Power Tool relies on SQL marked transactions to keep consistency across the TFS (and dependency products) databases. The SQL marked transaction implementation in the Backup/Restore Power Tool requires the SQL recovery mode to be set to Full. Thanks to the TFS product team for making this clear to me! Switching permanently to a Simple recovery mode could possibly result in a rollback to inconsistent TFS databases. More details on marked transactions can be found here.

A temporary solution is to manually switch to Simple recovery mode, shrink the log files and then switch back to Full recovery mode. The problem is that you would need to do this sometimes when the log files are getting "too big". A better solution might be to automate and schedule these actions for all involved TFS databases.

Here’s a sample SQL script that you might use:

ALTER DATABASE [<DatabaseName>] SET RECOVERY SIMPLE WITH NO_WAIT

USE [<DatabaseName>]

GO

DBCC SHRINKFILE (N'<DatabaseName>_log’ , 0, TRUNCATEONLY)

GO

ALTER DATABASE [<DatabaseName>] SET RECOVERY FULL WITH NO_WAIT

Timeout issues + log file sizes will be fixed in the next TFS Power Tool release (probably Q1 2011).

[Update March 13, 2011]

With the release of the new TFS Power Tools (March 2011), the timeout issue has been resolved. Note that you must not forget to disable the workaround script to shrink the logfiles.


Follow

Get every new post delivered to your Inbox.