Skip to content

Instantly share code, notes, and snippets.

@akanieski
Last active April 28, 2021 14:35
Show Gist options
  • Save akanieski/5cf1ab9045fe7adab5457aa4db6df14d to your computer and use it in GitHub Desktop.
Save akanieski/5cf1ab9045fe7adab5457aa4db6df14d to your computer and use it in GitHub Desktop.
A query to find large TFVC files and the perpetrator
DECLARE @minSizeInMb INTEGER = 0;
SELECT ProjectName
,SUBSTRING(_filePath, 1, LEN(_filePath) - 1) AS FilePath
,ChangeSet
,SizeInMB
,CommittedBy
,CommittedByEmail
,CommitDate
FROM (
SELECT ref.FileId
,proj.ProjectName
,CASE ver.VersionTo
WHEN 2147483647
THEN ver.VersionFrom
ELSE ver.VersionTo
END AS ChangeSet
,REPLACE(REPLACE(REPLACE(ver.FullPath, '$\' + REPLACE(LOWER(cast(proj.ProjectSK AS VARCHAR(255))), '-', '"'), '$'), '"', '-'), '>', '_') AS _filePath
,Round(cast(cast(meta.FileLength AS FLOAT) / 1024 / 1024 AS FLOAT), 2) AS SizeInMB
,usr.UserDisplayName CommittedBy
,usr.UserEmail CommittedByEmail
,chn.CreationDate CommitDate
FROM [dbo].[tbl_FileMetadata] meta
LEFT JOIN dbo.tbl_FileReference ref ON ref.ResourceId = meta.ResourceId
LEFT JOIN dbo.[tbl_Version] ver ON ver.FileId = ref.FileId
AND ver.ItemType = 2
LEFT JOIN AnalyticsModel.tbl_Project proj ON proj.ProjectSK = convert(UNIQUEIDENTIFIER, REPLACE(SUBSTRING(UPPER(ver.FullPath), 3, charIndex('\', ver.FullPath, 3) - 3), '"', '-'))
LEFT JOIN [tbl_ChangeSet] chn ON chn.ChangeSetId = CASE ver.VersionTo
WHEN 2147483647
THEN ver.VersionFrom
ELSE ver.VersionTo
END
LEFT JOIN [AnalyticsModel].[tbl_User] usr ON usr.[SpsUserId] = chn.CommitterId
WHERE ver.FullPath IS NOT NULL
AND Round(cast(cast(meta.FileLength AS FLOAT) / 1024 / 1024 AS FLOAT), 2) >= @minSizeInMb
) AS Results
ORDER BY SizeInMB DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment