Marking transactions on Team Foundation Server’s databases

As we know, in the Team Foundation Server PowerTools for the 2010 version and built-in in the 2012 version we have a Backup&Restore utility which is added to the Administration console for…backup purposes J

But it’s not always feasible: in some organization this kind of tooling could not be allowed, and therefore we have to follow the old manual procedure documented in MSDN.

It’s a little bit tedious, as it’s fully manual and we have to repeat this for each database in our Team Foundation Server deployment, so it can be error-prone, and during troubleshooting sessions it can lead to issues.

So, in order to minimize this, there’s a useful step to follow to track down all the transactions on the TFS’ databases: the transaction table.

It’s fairly easy: we have to create an empty table in each database, using the following script:

   1:  Use TFS_Configuration 

   2:  Create Table Tbl_TransactionLogMark 

   3:  ( 

   4:   logmark int 

   5:  ) 

   6:  GO 

   7:  Insert into Tbl_TransactionLogMark (logmark) Values (1) 

   8:  GO 

After that, we can create a Stored Procedure which marks every transaction on the TFS’ databases, and reports back to the table:

   1:  Create PROCEDURE sp_SetTransactionLogMark 

   2:  @name nvarchar (128) 

   3:  AS 

   4:  BEGIN TRANSACTION @name WITH MARK 

   5:  UPDATE TFS_Configuration.dbo.Tbl_TransactionLogMark SET logmark = 1 

   6:  COMMIT TRANSACTION 

   7:  GO 

Then you can run the Stored Procedure, and mark the transactions with a mark. As MSDN states, “TFSMark”.

   1:  EXEC sp_SetTransactionLogMarkAll 'TFSMark'

   2:  GO

So you can automate this Stored Procedure, and use it as a ‘filtered log’ on your database activities.

It’s nothing new in the end, but as it’s hidden inside the MSDN documentation I’ve never seen it out there, and IMHO it was worth of a mention.