Azure DevOps Server database growing out of control

Question

My Azure DevOps Server 2020 Update 1.1 database has grown to over 1TB, and I see no reason for it becoming so big. I have quite aggressive retention policy (all builds are set to be deleted as soon as possible, and test results are to be deleted with builds), but it does not help.

I suspect some cleanup job has stopped working, but I cannot figure out what/where.

Here are some results from the database, scoped to the most problematic build definition.

SELECT COUNT(*) FROM Build.tbl_Build where DefinitionId = 244
-- Result: 79

SELECT BuildDeleted, COUNT(*) FROM tbl_BuildConfiguration 
WHERE BuildDefinitionId = 244
GROUP BY BuildDeleted
-- Result BuildDeleted=0: 1085
-- Result BuildDeleted=1: 18722

Table tbl_Content is at 919GB, which breaks down to this:

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 / 1024 AS BlobSizeInGB
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

-- Result (rounded):
-- TeamTest         573
-- FileContainer    295
-- VersionControl    11
-- WorkItemTracking   8
-- BlobStore          2
-- CodeSense          0
-- Generic            0
-- ProcessTemplate    0

Does this answer helps? https://stackoverflow.com/questions/52953160/how-to-shrink-the-tfs-2017-fast-growing-tbl-content-table — Bowman Zhu-MSFT, Mar 21 '22 at 02:40
Unfortunatly not, there is not a problem with retained builds, its rather deleted builds that have leftover files. — Stein Rustad, Mar 26 '22 at 17:01

nasrin mirzaei · Answer 1 · 2023-07-30T10:39:13.823

please run this query:

  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

It will show you what is happened to growing out of control. If the result show that build, ... is bigger than others please check setting for retention, and sometimes you need to delete old builds by hand. After you need to shrink your database data. TFS does not delete anything completely, and you should set after how many days you need to remove them, but you need a job to shrink data of your database to decrease database's capacity, also I did these queries too and after that I shrank it and I could decrease it from 90G to 46G

 exec [dbo].[prc_DestroyDeletedBuilds] 1

 exec [dbo].[prc_CleanupDeletedFileContent] 1

 exec [dbo].[prc_DeleteUnusedFiles] 1,1,1000,0

Azure DevOps Server database growing out of control

1 Answers1