Analyze the TFS Team Project Collection Database Size

As with every TFS installation, the Team Project Collection (TPC) database begins to grow. Now and then, there are questions like “What kind of data is filling up our disks?”. So let’s have a look on how to answer those kinds of questions.

Do not manually manipulate any data or schema of TFS databases!

Everything described in this blog post is just about reading data and/or information form the TFS databases. Please keep in mind that you should never alter any schema information or data. You will loose support from Microsoft if you do so. All the information in this post was disclosed by Microsoft in the developer community. Please check this link for the details.

The first thing to do, if we want to have more information about the disk space usage, is to analyze the database itself. There is a build in procedure which provides information about the db size, the actual data, the unallocated space, indexes and so on.

1
exec sp_spaceused 

This will provide the a result like this:

So far so good, but we need to see what kind of data actually uses space in our database. Generally, the most storage is used for BLOBs. Therefore we will now analyze the blob sizes. The different types of blobs can be identified by their owner. To summarize the blob sizes grouped by owners, we can use the following SQL script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT Owner = 
CASE
WHEN OwnerId = 0 THEN 'Generic' 
WHEN OwnerId = 1 THEN 'VersionControl'
WHEN OwnerId = 2 THEN 'WorkItemTracking'
WHEN OwnerId = 3 THEN 'TeamBuild'
WHEN OwnerId = 4 THEN 'TeamTest'
WHEN OwnerId = 5 THEN 'Servicing'
WHEN OwnerId = 6 THEN 'UnitTest'
WHEN OwnerId = 7 THEN 'WebAccess'
WHEN OwnerId = 8 THEN 'ProcessTemplate'
WHEN OwnerId = 9 THEN 'StrongBox'
WHEN OwnerId = 10 THEN 'FileContainer'
WHEN OwnerId = 11 THEN 'CodeSense'
WHEN OwnerId = 12 THEN 'Profile'
WHEN OwnerId = 13 THEN 'Aad'
WHEN OwnerId = 14 THEN 'Gallery'
WHEN OwnerId = 15 THEN 'BlobStore'
WHEN OwnerId = 255 THEN 'PendingDeletion'
END,
SUM(CompressedLength) / 1024.0 / 1024.0 AS BlobSizeInMB
FROM tbl_FileReference AS r
JOIN tbl_FileMetadata AS m
ON r.ResourceId = m.ResourceId
AND r.PartitionId = m.PartitionId
WHERE r.PartitionId = 1
GROUP BY OwnerId
ORDER BY 2 DESC

This will provide the a result like this:

This result now shows where data large blocks come from. The problem is that normally the file container is just the huge part and we still cannot dig through the details. Time to drill down the file container storage.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT CASE WHEN Container = 'vstfs:///Buil' THEN 'Build'
WHEN Container = 'vstfs:///Git/' THEN 'Git'
WHEN Container = 'vstfs:///Dist' THEN 'DistributedTask'
ELSE Container 
END AS FileContainerOwner,
SUM(fm.CompressedLength) / 1024.0 / 1024.0 AS TotalSizeInMB
FROM (SELECT DISTINCT LEFT(c.ArtifactUri, 13) AS Container,
fr.ResourceId,
ci.PartitionId
FROM tbl_Container c
INNER JOIN tbl_ContainerItem ci
ON c.ContainerId = ci.ContainerId
AND c.PartitionId = ci.PartitionId
INNER JOIN tbl_FileReference fr
ON ci.fileId = fr.fileId
AND ci.DataspaceId = fr.DataspaceId
AND ci.PartitionId = fr.PartitionId) c
INNER JOIN tbl_FileMetadata fm
ON fm.ResourceId = c.ResourceId
AND fm.PartitionId = c.PartitionId
GROUP BY c.Container
ORDER BY TotalSizeInMB DESC

This will provide the a result like this:

In the above example, we can identify that build artifacts are taking a huge amount of space in the database. So in this case, we would have to analyse the retention policy settings in TFS in order to get rid of old builds which are not used anymore.