Azure DevOps–Why is my database so big?

When preparing an Azure DevOps Server migration, the customer asked me why there Azure Devops databases grew so fast.

There are 2 queries that can help you answer this question.

• The first query gives a general overview of the different parts:

	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

view raw DatabaseUsage.sql hosted with ❤ by GitHub

The output of the first query returned the following results in this case:

Owner	BlobSizeInMb
VersionCVersionControl	64992.008339881835
FileContainer	59599.479797363281
TeamTest	44243.018857001953
WorkItemTracking	23674.237445831054
CodeSense	989.945262908203
ProcessTemplate	125.949053763671
Generic	54.288738250000

• The second query goes in more detail to differentiate between source control and build:

	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

view raw DatabaseUsageDetails.sql hosted with ❤ by GitHub

The output of the second query returned the following results in this case:

FileContainerOwner	TotalSizeInMb
Build	56681.754142760742
Git	875.809092521484
vstfs:///Rele	683.627062796875
pipelines://b	271.543574333007
DistributedTask	1.143143653320

They started storing there build artifacts in the database, this explained why there Azure DevOps databases exploded so much.