Our TfsVersionControl database was at nearly 20 gigs when I started digging into why some source control actions were so slow. While 20 gigs is relatively small in terms of enterprise databases, it’s huge for our small source tree. Simple tasks like getting latest on a branch had a noticeable 5-30 second spin-up time while they queried the database.
The largest table turned out to be tbl_LocalVersion, which clocked in at 10 gigs. This table tracks the links between versioned items and the local workspaces of the developers. Listing out the workspaces with “tf workspaces /owner:*” showed the problem, we had 500+ workspaces, most of them belonging to developers who were no longer here. Deleting these workspaces also deletes their associated LocalVersion records. A quick script to run “tf workspace /delete “my dev branch;johnsmith”” for all the old workspaces brought us down to about 40 workspaces and a LocalVersion table of less than 3 gb.
To get a rough idea of who’s using what, run this in TfsVersionControl:
SELECT i.DisplayName, COUNT(DISTINCT w.WorkspaceId) AS Workspaces, COUNT(1) AS LocalVersionCount
FROM tbl_Identity i WITH (NOLOCK) INNER JOIN tbl_Workspace w WITH (NOLOCK)
ON i.IdentityId = w.OwnerId INNER JOIN tbl_LocalVersion v WITH (NOLOCK)
ON w.WorkspaceId = v.WorkspaceId
GROUP BY i.DisplayName
Trimming out these workspaces has made the single largest improvement in the perceived performance of TFS out of anything I’ve tried so far.