Determining the size of deltas in TFS

Question

Our TFSVersionControl database has grown significantly in the past couple years, and is edging on 80GB. Unfortunately, we're in an environment where every gig of data storage is internally charged at a high rate, so there's lots of focus on keeping storage growth to a minimum.

I believe the majority of growth is happening because we chose to store binary files in our repository. This is something we will be remedying in the medium term.

In the short-term, there are a few places where we do not need to keep a history of our binaries. Particularly in our mainline branch and our development branch, so we're looking into doing a TF Destroy on these binaries and recreating them as part of the upcoming release.

What I'd like to know is: Is there any way to run a query against the TFSVersionControl database to understand which files are storing deltas that are over a given size?

Ideally, what I'd like to know is for a given path (item spec), for each file, the base size, and the total size of the deltas.

Answer 1

Just like asking someone else will often drive you to find your own answer, I did some additional digging, and came up with this:

select ver.VersionFrom, ver.Command, ver.ChildItem, tf.*, ct.CreationDate, ct.OffsetFrom, ct.OffsetTo, DataLength(ct.Content) as Size
from tbl_version ver with (nolock)
inner join tbl_file tf with (nolock) on tf.FileId = ver.FileId
inner join tbl_content ct with (nolock) on ct.FileId = tf.fileid 
where parentpath = '$\ProjectName\Branch\Folder\'
ORDER BY ver.ChildItem, Ver.VersionFrom

--where fullpath = '$\ProjectName\Branch\Folder\FileName.cs\'

The query as written will iterate through all files in a particular path and will retrieve a record per checkin. The calculated Size field will show you the size in bytes of the delta. I'm not sure if this is compressed size or "actual" size.

The commented "where" statement will show you the same for an indvidual file.

Note that the typical forward slashes ("/") are stored in the database as backslashes ("\"), and there is always a trailing backslash at the end.

If you pull this data into Excel, you can quickly create a pivot table on it to calculate the sizes (or you can add them up manually).

Answer 2

I think this page may be what you're looking for.