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
-- parent work item
ID AS WorkItemID,
-- name of the attachment file
OriginalName AS AttachementName,
-- attachment comment
-- file size
[Length] AS [Size],
-- whether attachment was deleted
CASE WHEN RemovedDate = '01/01/9999' THEN 0
ELSE 1 END AS Deleted
-- 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,
FROM TfsWorkItemTrackingAttachments.dbo.Attachments Attachements,
WHERE Attachements.FileGuid = WorkItems.FilePath
AND WorkItems.RemovedDate <> '01/01/9999'
AND WorkItems.FldID = 50
-- When absolutely sure - delete the orphans
-- join to WIT tables to identify orphans
WHERE FileGuid IN (SELECT FilePath
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).