Why Does TFS Allow Orphaned Content and How Do I Get Rid of It?

Question

My TfsVersionControl database has grown to 40+ GB in size. We recently did a TFS Destroy on a folder tree that should have cleared up at least 10 GB but instead it seemed to have no effect.

When I look at the tables in TfsVersionControl, I am first shocked to see that there are no foreign keys at all in the database. Running a few queries, I see that there is some orphaning going on:

• tbl_Content has 13.9 GB of records that don't have a related tbl_File record
• tbl_File and tbl_Content have 2.4 GB that don't have a related tbl_Namespace record

The cleanup job seems to be running nightly (prc_DeleteUnusedContent) and running it against the database manually doesn't remove any orphans. I see in the log for the cleanup job that it failed on 3/16, which is the morning after I destroyed the large amount of data. The error was due to a full transaction log.

Could that error be the reason I'm left with all this orphaned data that can't be deleted? How can I permanently destroy this unneeded content?

Answer 1

See the blog post on MSDN http://social.msdn.microsoft.com/Forums/en-SG/tfsversioncontrol/thread/5f3f8916-1c6d-46f7-9dae-2cdaeaee98db

As noted by Chandru from the TFS team:

This is due to a bug in TFS 2008 - where if the nightly job failed, it caused this problem. Please contact microsoft support and they can provide you a fix for it. Please do not attempt to fix this yourself.

Answer 2

After a long back and forth with the folks at Microsoft, it turns out this is a known bug in the failure of some cleanup processes. There's a knowledge base article here: http://support.microsoft.com/kb/974596

The hotfix described is obsolete if you've already installed TFS 2010.

In addition, the tech at Microsoft had me run a DELETE statement on tbl_Content to delete all records which didn't point to an actual tbl_File record. I'd post the SQL, but don't want to be responsible for anyone copying and pasting. It's pretty self explanatory and as easy as you think.