Analyze the TFS Team Project Collection Database Size

TFS

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:

DB Name Size Unallocated Reserved Data Index Unused
Tfs_Demo 805’026 MB 175’176 MB 545’748 MB 542’897 MB 1889 MB 962 MB


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:

Owner Size
FileContainer 314’683 MB
VersionControl 43’077 MB
BlobStore 52’704 MB
TeamTest 10’242 MB
WorkItemTracking 1’830 MB
CodeSense 27 MB
ProcessTemplate 25 MB
Generic 16 MB


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:

Container Type Size
Build 272’755 MB
Git 41’364 MB
vstfs:///Rele 447 MB
DistributedTask 97 MB


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.

Tags:
');