Before I do an upgrade I always ask for the size of the TFS database set. It provides me the information on how long an upgrade might take. The queries below, also provides information of attachments that might be taking up extra disk space. By reducing the overall database set, the whole upgrade process will go faster. Plus, it is a good time to do some house cleaning.
Gather the database size information
To get the database sizes, you can run the following query
source of the queries: Guide to reduce TFS database growth using the Test Attachment Cleaner tested on TFS 2013 RTM
To find out the attachments that are taking up space, you can run this set of queries
Find out which team projects have possibly too large attachments
Check which attachment types take up the most space
Check which file types, by their extension, takes up the most space
These three queries can be ran together
To get an month range of when the attachment have been add you can run the following query
source of the queries: TFS2010: Test Attachment Cleaner and why you should be using it tested on TFS 2013 RTM
To get more detail information on the tables space, you can run the following query
This give a output of tables information in the database.
Reducing the database size
Identifying the issues is always the first steps. What to do to reduce the size of the database will take a few actions. If you have a small manageable database size like my examples above, then no actions would be required. But if you need to reduce the size here are a few tools to help.
Warning: Removing any files should be done with caution and NEVER DIRECTLY FROM THE DATABASE. As always before you modify, update or delete, take a full backup of the databases
- Removing large unused test attachments
- Removing old un used team project by using the TFSDeleteproject command
- TFSDeleteProject: Deleting Team Projects – Warning: As always, make sure the project being deleted is not being used.
- Remove old workspaces
- Using TFS Sidekick to find and remove old workspaces from non-active team member
After the removal of old and unused artifacts in the TFS database it is good to re-index and reduce the database size using SQL commands.
Warning: These actions should be done off hours to reduce any impact to the teams.
Keeping up on the TFS database growth is not a everyday administrative task. However, before doing an upgrade, it is a great time to clean out some of the old artifacts that are causing database bloating. It also help in reducing the upgrade time if you do have a large database.
I also have to give credit to the following site for their SQL code and information on reducing the TFS database. I have tested all the script on a TFS 2013 instance and verified that the scripts do worked