azure-devops azure-pipelines azure-devops-server-2019 azure-devops-server

Since upgrade, on-prem AzureDevops Server 2019 is retaining 10x build data in dbo.tbl_content

My on-prem, Azure DevOps 2019's backups are showing an unsubstainable increase in size of the .mdf file

query1 shows that it's the "dbo.tbl_content" table
query2 shows that it's "FileContainer" at 112GB.
query3 shows that it's pipelines://b at 93GB.
query4 shows that the size used has gone up from 1GB a month, to the unsubstainable 10GB per month. This occurred in Jan 2020, when possibly coincidentally, we upgraded from TFS18 to AzureDevOps19.

So,I believe I'm looking for a build pipe (not release pipe) that needs cleaning up? Historically, we've tried to keep 366 days worth of old build logs but at the rate we're going we won't make it.

We've got about 40 build pipes (some historic, that no longer run), inc 4 triggered on commit (CI).

re: retention policy...

typical CI build retention policy. Days to keep: 10 Min to Keep: 1
typical RC build retention policy. Days to keep: 180 Min to Keep: 50
from: DefaultCollection/Base/_settings/buildqueue... Maximum retention policy / Days to keep: 183 Min to Keep: 55 Default retention policy / Days to keep: 15 Min to Keep: 1 Permanently destroy builds / Days to keep build record after deletion: 366 <- I reduced this yesterday down from 7000

Any help appreciated here, but specifically:

How can I track down the specific build that's causing the problem? and how can I fix it?
Is there any tooling that will show me where problems lie. e.g. TFS used to have a health audit tool, but I can't see it?

query1 SELECT TOP 10 o.name, SUM(reserved_page_count) * 8.0 / 1024 SizeInMB, SUM(CASE WHEN p.index_id <= 1 THEN p.row_count ELSE 0 END) Row_Count FROM sys.dm_db_partition_stats p JOIN sys.objects o ON p.object_id = o.object_id GROUP BY o.name ORDER BY SUM(reserved_page_count) DESC

query2 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

query3 SELECT CASE WHEN Container = 'vstfs:///Buil' THEN 'Build' WHEN Container = 'vstfs:///Git/' THEN 'Git' WHEN Container = 'vstfs:///Dist' THEN 'DistributedTask' WHEN Container = 'vstfs:///Rele' THEN 'Release' ELSE Container END AS FileContainerOwner, SUM(fm.CompressedLength) / 1024 / 1024 AS TotalSizeInMB FROM (SELECT DISTINCT LEFT(c.ArtifactUri, 13) AS Container, fr.ResourceId, ci.PartitionId FROM tbl_Container c with (nolock) 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

query4 Select DATEPART(yyyy, CreationDate) as [year], DATEPART(mm, CreationDate) as [month], SUM(DATALENGTH(Content)) / 1048576 as [Size in Mb] From tbl_Content With (nolock) Group by DATEPART(yyyy, CreationDate), DATEPART(mm, CreationDate) Order by DATEPART(yyyy, CreationDate), DATEPART(mm, CreationDate)

Related question: TFS2015 tbl_Content increase

Solution

You could try to run below query to narrow down the date:

SELECT ci.ContainerId,
c.ArtifactUri,
c.Name,
c.DateCreated,
SUM(fm.FileLength)
FROM tbl_ContainerItem ci
JOIN tbl_FileReference f
ON f.FileId = ci.FileId
JOIN tbl_FileMetadata fm
ON fm.PartitionId = 1
AND fm.ResourceId = f.ResourceId 
LEFT JOIN tbl_Container c 
ON c.ContainerId = ci.ContainerId 
AND c.PartitionId = 1 
WHERE f.PartitionId = 1 
AND ci.PartitionId = 1 
GROUP BY ci.ContainerId, c.ArtifactUri, c.Name, c.DateCreated

And since it's related to build, kindly check if test report caused this. You could refer detail actions in this thread: TFS database growing too large

Besides, you could also try shrinking the transaction log in TFS/Azure DevOps Database.