Saturday, March 28, 2009

TFS Administrator chores – dealing with the space offender

These are the days of cheap storage - but even the cheap storage may run out. And running Team Foundation Server storing artifacts in its (multiple) databases may use up your space rack faster than you might have expected (and if you want to know what to expect, refer to this classical post by Buck Hodges on database size calculations).

If that happens, the most probable culprit is version control database (TfsVersionControl) – in other words, all these files that people check in into version control. The size of the file matters because TFS stores difference only for each new revision of “small” files but for the “large” files every new revision gets full-blown copy (by default TFS considers the file to be large if it is over 16 Mb - read more on that topic in my previous post).

There are several ways of making sure that your users do not fill up your version control with memory dumps, images of installation CDs and such. Mind you – I am not saying that large files do not belong to version control; I am saying that the addition of large files should be a) conscious step and b) “revisionless” (i.e. with no versioning).

Myself, I have been always ambivalent about storing large binary thingies in source control – on one hand, you get all content in one place (which is mighty convenient for builds etc.), on the other hand, many users will probably check in the content that does not belong in source control. So here is my hit list of  measures to deal with large files in version control

  • Educate your user – make sure your average user understands that DVD ISO added to version control ends up being transmitted and stored in the database; perhaps what the user is looking for is file server, not version control
  • Make user aware of his actions – it is possible to write check-in policy that would alert the user at the time of check-in, that the files being checked in are large and perhaps should not be in version control. And then, even if the user decides to override the policy you may run report on policy overrides
  • Monitor your storage – if high level prevention and low level prevention fail, you can query the database to identify the offending files. The query below (with usual caveats – it is AS IS etc.) will give you a list of large files in the database (it will not take into account the summary size of all versions, only the latest version):
DECLARE @LargeFile int;
-- return files larger than 16 Mb
SET @LargeFile = 16 * 1024 * 1024; 
USE TfsVersionControl; –– use source control DB 
SELECT -- item path 
    Versions.ParentPath + Versions.ChildItem AS ItemPath,
    -- size of latest version in DB 
    Files.CompressedLength AS DatabaseSize, 
    -- size of original file
    Files.FileLength AS [Size], 
    -- whether item deleted
    CASE WHEN Versions.DeletionId = 0 THEN 0 
        ELSE 1 END AS Deleted 
FROM tbl_File Files, tbl_Version Versions
WHERE -- get item latest version 
    Versions.VersionTo = 2147483647 
    -- join to table with sizes
    AND Versions.FileId = Files.FileId 
    -- return only large files
    AND Files.CompressedLength > @LargeFile 
ORDER BY ItemPath;

I would be happy to hear your horror stories of the application of the above query; mine was nothing more than a bunch of ISO images checked in :)

Thanks for reviewing the query go to Chandru Ramakrishnan

Mirrored from MSDN blog

Moving on

As you might now, from November ‘08 I am working at Microsoft, and as every MS employee I am now entitled to Community Server powered blog at (mine is

Though Blogger is a convenient platform, Community Server is ways better, and thus I will be making MSDN my new [blog] home. So update your reader with the new feed! But if you don’t – do not worry, I will be mirroring TFS related posts here.

See you around!

Something good to read

This is a short post to let you know that there is one new blog of note that you should add to your RSS reader. Welcome (back) Richard Berg, blogging about TFS,  PowerShell and more at Until recently, Richard used to work at Microsoft, and if you ever asked a question at MSDN TFS Version Control forum, you probably had it answered  by Richard.

His new blog already has a whole lot of information about TFS PowerShell cmdlets, so stay tuned for more!