TFS Pre Upgrade preparation best practices.
Most of the time when we want to upgrade TFS from one version to another, we only worry about the hardware involved, the Operating systems and SQL server versions involved, there is one another very important item to be considered, that is TFS databases. We forget to check whether they are really ready, tidy and fit for upgrade.
This blog discusses the various steps to be taken prior to moving a TFS collection and TFS Configuration database to another server for upgrade. Steps such as removal of the unused workspace translates in to good amount of time saving during upgrade.
1. Removing unwanted items before upgrade
The unwanted items could be in any one of the following form.
1. Orphaned Version control tree’s
2. Deleted Version control tree’s
3. Build definitions that are no longer in use
4. Build results and test case attachments
5. Orphaned workspaces or workspaces that are no longer in use
6. Delete workitems that are no longer needed
2. Shrink the database
3. Indexing the TFS databases
4. Delete unwanted projects
Here are the ways to identify and clean them up:
1. How to identify orphaned /unused version control trees
Mere deletion of the version control tree doesn’t help, we need to destroy them using the following command line
https://msdn.microsoft.com/en-us/library/bb386005(v=vs.100).aspx
2. How to delete build definitions not in use
https://msdn.microsoft.com/en-us/library/ms181719(v=vs.110).aspx
3. How to delete a build and How to identify orphaned build data
https://msdn.microsoft.com/en-us/library/ms244360(v=vs.100).aspx
4. Orphaned workspaces or workspaces that are no longer in use
The following command line will list all the workspaces matching the string name "wsp"
tf workspaces /owner:* /server:https://yourtfsservername:8080 | findstr wsp
The following command line will help delete a particular workspace, please note the deletion is irreversible, loss of data will be irrecoverable, so a meticulus approach to delete is a must.
tf workspace /delete /server:https://yourtfsservername:8080 name;domain\username
You may also use tools such as Workspace Sidekick from https://www.attrice.info/cm/tfs/ to identify and manage these workspaces
5. Shrink the database (you need your DBA help)
The following can be run after making a backup
ALTER DATABASE Tfs_database SET RECOVERY SIMPLE
DBCC SHRINKFILE (Tfs_database_Log, 1)
6. TFS has the capability to index its databases and keep it tidy, this process is done using a background job called “Optimize databases” , in a situation if this is failing for some reason
Use the following steps to reindex before detaching/ making backup for upgrade, you need SQL privileges to perform these steps, upgrade steps run on a properly indexed and statistics updated database is faster and saves lot of time.
General database index update steps:
Run the below steps one after another against all the TFS databases:
1. Update the statistics & rebuild the indexes for all the User DB’s which is used for the TFS application using below commands,
Exec sp_MSforeachtable "dbcc dbreindex('?')"
go
** After running the above command then run the below command. Please do not run both the commands at the same time.
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
go
2. Once the above changes are done please run the below commands to clear the procedure, buffer & system cache.
--Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.
DBCC FREEPROCCACHE
go
-- Removes all clean buffers from the buffer pool.
DBCC DROPCLEANBUFFERS
Go
-- Releases all unused cache entries from all caches. The SQL Server 2005 Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries.
DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
go
Cheers!
Content created by – Vimal Thiagaraj
Comments
Anonymous
November 05, 2015
Nice post. Will shrinkDB and re-indexing all collection DBs have a performance impact ?Anonymous
November 05, 2015
Hi Ravi, This should not have a performance impact. In fact, you can do them as part of regular database maintenance. The TFS optimization jobs do index, but for large databases, we recommend updating the statistics and indexing. For the upgrade, the shrinkDB will reclaim some space, and the re-indexing will speed up the upgrade process.