CODEWITHSUNDEEP
tfs
Henrik Fransas
Henrik Fransas

Reputation: 1107

Find what project in TFS that is taking up space

We need to figure out what project is making our TFS database to grow out of hand and we found this script but it does not work with the latest version of TFS.

Does anyone have any updated script for this?

Upvotes: 1

Views: 2114

Answers (1)

PatrickLu-MSFT
PatrickLu-MSFT

Reputation: 51093

First you could run a SQL script to show an increase of the tbl_Content over the last several months:

SELECT DATEPART(yyyy, CreationDate) AS [year]
    ,DATEPART(mm, CreationDate) AS [month]
    ,COUNT(*) AS [count]
    ,SUM(DATALENGTH(Content)) / 1048576.0 AS [Size in Mb]
    ,(SUM(DATALENGTH(Content)) / 1048576.0) / count(*) AS [Average Size]
FROM tbl_Content
GROUP BY DATEPART(yyyy, CreationDate)
    ,DATEPART(mm, CreationDate)
ORDER BY DATEPART(yyyy, CreationDate)
    ,DATEPART(mm, CreationDate)

This will reflect if your collection database have a abnormal increase. Then you could look at look at the distribution of "owners" for the data in tbl_Content such as VersionControl, Work Item, Test... Which area has the biggest percentage.

Detail SQL script:

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

(For more information, please refer the link @jessehouwing provided in the comment.)

To reduce the size of the tbl_Content table, you could refer to this blog: TFS tbl_Content Table and Database growth out of control

  1. Clean some old workspaces that you doesn't need any more.
  2. Run the tf destory command to delete those unnecessary source files permanently.
  3. Using TFS power tool to clean Test attachments and test results.

Upvotes: 3

Related Questions