Share via


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