TFS Administrator chores – space offender strikes again!

In my previous post I talked about management of large files in TFS version control database. Today I’d like to talk about what you can do to optimize space management in work item tracking database.

As you know, it is possible to add file attachments to Work Item, with the maximum attachment size of 2Mb (by default); but most people who use attachments with WI change that limit to something larger (this MSDN article details how to change the maximum attachment size), since default frequently does not suffice for video captures and such.

Which naturally brings us to the question – if the maximum size set, say, to 32 Mb, how could one prevent misuse of the attachment feature?

There is nothing in Team Explorer UI to help you with figuring out the size of the added attachment; and nothing to prevent a user from adding however many large attachments (if they are not greater than maximum size). That leaves you with user education as a form of prevention; and to report the usage it is possible to run raw SQL on the relational database (all of the below queries are strictly AS IS etc.):

-- Query WIT database

USE TfsWorkItemTracking;

SELECT 

    -- parent work item 

    ID AS WorkItemID, 

    -- name of the attachment file

    OriginalName AS AttachementName, 

    -- attachment comment 

    Comment, 

    -- file size

    [Length] AS [Size], 

    -- whether attachment was deleted

    CASE WHEN RemovedDate = '01/01/9999' THEN 0 

              ELSE 1 END AS Deleted 

FROM WorkItemFiles    

    WHERE 

    -- File attachments only

    FldID = 50

    -- return only large files

    AND    [Length] > @LargeFile 

The query will give you the list of WI with large attachments, so you could figure out whether this feature is used in a sensible way.

If you look at the query closely, you’ll notice that the attachment in the database can be removed from WI and still exist in the database. What does that mean, say you? Whereas with version control one can delete item (where the item still will be in DB) and then destroy it (where item will be purged from DB), there is no such feature with Work Item attachments.

It turns out when you delete attachment from Work Item, the actual content is never deleted from database unless you do it manually. There is even helpful but incredibly well-hidden and vague article in MSDN on the subject, titled “How to: Delete Orphaned Files Permanently”.

That means even if you have managed to delete large attachments from WI, your job to recover the space is still half-done, and you need to actually delete the attachment content from the database.

The query below will enumerate all orphaned (deleted from Work Items, but still in DB) attachments, whereas subsequent query can be used to actually purge the deleted items from the database.

-- Query for all orphaned attachments

SELECT WorkItems.ID AS WorkItemID, 

        WorkItems.OriginalName AS AttachementName,

        WorkItems.Comment 

FROM TfsWorkItemTrackingAttachments.dbo.Attachments Attachements, 

        TfsWorkItemTracking.dbo.WorkItemFiles WorkItems

    WHERE Attachements.FileGuid = WorkItems.FilePath 

        AND WorkItems.RemovedDate <> '01/01/9999'

        AND WorkItems.FldID = 50

-- When absolutely sure - delete the orphans

DELETE 

    FROM TfsWorkItemTrackingAttachments.dbo.Attachments

-- join to WIT tables to identify orphans

WHERE FileGuid IN (SELECT FilePath 

        FROM TfsWorkItemTracking.dbo.WorkItemFiles

        WHERE RemovedDate <> '01/01/9999'

        AND FldID = 50)

Purging orphans seems to me a good candidate for the recurring job (not sure why it is not part of core TFS setup).

Mirrored from MSDN blog