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).