Reduce the size of TFS databases after cleaning some source control files
Recently we have noticed that some of our clients have huge TFS databases. Those clients have used TFS for a long time, with very old projects and files that they don’t need anymore.
The huge data mainly affects the backup process from time and size perspective. For some clients this has affected performance, so we decided to clean up to the data.
In this blog, we just want to share our experience.
All the steps and screenshots below have been done on a clean TFS 2010 with SP1 environment. “It was a new Brain Killer VM”
Before starting we have checked tfs_DefaultCollection database size, it was about 120MB:
From SQL Server Management Studio:
First, we checked-in a huge file to one of the team projects in the default collection. The file size was about 1.5 GB:
After checking in the file, the size of the default collection database become about 1.5 GB, plus a .6 GB log file.
From SQL Server Management Studio:
We checked the default collection table and found that the largest table was tbl_content
We then started the cleanup process for this file by deleting the file from source control and checking in the pending delete change.
Nothing changed after checking the change in. The size of the physical database files and tbl_content was not reduced.
This is normal. Deleting the file will not physically delete it from the database. To completely delete the file you have to destroy it.
We typed the following command: (We should have included "/startcleanup" on the command)
Tf destroy “$/tailspin toys/ SW_DVD5_SharePoint_Server_2010w_SP1_64Bit_English_MLF_X17-76080.ISO”
Next, we checked the size of the database files in the file system and on SQL Server Management Studio, and the tbl_content size. Unfortunately, nothing changed.
TFS will not immediately clean the unneeded files. The clean-up process occurs when the database maintenance cleans up all the files that are no longer referenced by Visual Studio Team Foundation Server. By default, the clean-up is scheduled to run once each day.
This is why we should have included “/startcleanup” in the destroy command. This argument will force the file content clean-up process to start immediately after destroying the file.
Without this argument, you have to wait for a whole day to see the new database size. We found a workaround by running the “prc_DeleteUnusedContent” stored procedure in the collection database.
Five minutes after running the command, the size of the tbl_content decreased
However the database size on SQL sServer Management Studio has increased, (although with some available space).
The database file size was the same, but the log file was huge. (This is expected behavior.)
Finally, after performing a backup of the database and then shrinking it, the size of the database was reduced.
It’s even smaller than the size before check-in the file, as the cleanup process has cleaned other unneeded items.