Team foundation Server 2012 Database size

Question

I have a 400GB TFS database (tfs_DefaultCollection). I have ran the Attachment cleaner tool that has informed me it has deleted 200GBs of data. After this and querying the largest tables the row counts are the same and sizes haven't changed. The mdf file size remains the same and so does the top four tables. (tbl_FunctionCoverage, tbl_TestResult, tbl_BuildInformation and tbl_Content). I am assuming there is some form of tidy scripts I need to run maybe? I have executed prc_DeleteUnusedContent and prc_DeleteUnUsedFiles but I believe they are more for version control and workspaces as they made no changes.

I will shrink the database and reindex the tables but as the table row counts and sizes haven't changed i can't see it making much difference.

Any advice is appreciated.

Answer 1

So I think I may have solved my problem...

I wrote a small app to enumerate our build defnitions finding all those marked as deleted. For each deleted build I then deleted all associated test runs and lastly destroying the build. This took around 16 hours to run removing 25,000 builds and about 60,000 test runs. This doesn't appear to change a great deal in the database straight away but some of the tables did reduce in row count.

I then left the database a few days (turned into about 10) and background cleanup jobs that ran appeared to clean up a large amount of the data, they did however run for several days in doing this and in a live system I'm not sure on the performance impact.

Shrinking the database after this reclaimed about 160GB's (took around 10-12 hours) also disabling reporting and removing the Warehouse database (70GB) has bought the total down to database size to 175 GBS.