2

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

Nimantha
  • 6,405
  • 6
  • 28
  • 69
timB33
  • 1,977
  • 16
  • 33

1 Answers1

1

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.

PatrickLu-MSFT
  • 49,478
  • 5
  • 35
  • 62
  • 1
    Big thanks! this query helped me id a CI build, which I'd left switched to "system.debug=true" which had increased its size from 3MB to 300MB. I've already cloned and deleted this CI build (last night) so I'm hoping that it will get cleaned up sometime today thus releasing the space in tbl_content, is that correct? – timB33 May 29 '20 at 12:12
  • @timB33 Yes, thanks for your kindly sharing, always better when you fix it yourself. – PatrickLu-MSFT Jun 01 '20 at 02:01