Destroy Work Item : Is it really gone?
This week we had a situation where we needed to remove a work item from the TFS database completely. You can delete a work item in Team Explorer however this just marks the row in the database as deleted and as such your database will always grow, never shrink.
The Visual Studio Team System 2008 Team Foundation Power Tools give us the ability to destroy a work item with the ‘destroyWI’ call. Here’s the command line syntax:
tfpt destroywi /server:<SERVER> /workitemid:<ID>
The ‘intention’ is to delete all instances of the ID from the database, and all related data, and in truth it comes pretty close. However there is one table it misses: ‘WorkItemsWere‘
If you look at your TFS database instance you will notice there are actually a few databases. One of them is ‘WorkItemTracking’ and this database contains all the tables related to work items. The astute might also notice that each of the databases in their instance seems to match each of the web services provided by TFS. Coincidence?
In the ‘WorkItemTracking’ database you will see a number of tables that all contain work item related data. In particular, notice the two tables: ‘WorkItemsAre’ and ‘WorkItemsWere’. The ‘Are’ table contains the current state of a work item while the ‘Were’ table contains its history.
For example, work item 1234 is modified several times. First it is opened as a bug and assigned to ‘triage’. Then triage moves it to the developer who fixes the bug and resolves it with comments, where it is assigned back to the user who opened it. The bug isn’t fixed, some additional comments are inserted, its assigned back to the developer, and so on. If you are familiar with work items, you will be able to identify the ‘History’ section where you can view the audit history. This essentially is what is stored in the ‘WorkItemsWere’ table. Makes sense now right?
Yesterday I did a destroy of a work item and discovered that all the various tables had all instances cleaned up, except the ‘WorkItemsWere’ table. I contacted the VSTS team who indicated that this is indeed a bug and it is now logged in their own work item system (oh the irony!).
What to do in the mean time? Well they assure me it is safe to delete the orphaned database rows, despite the general consensus on the net not to mess with the database directly. I can confirm however that this is the only table with orphaned data. And I’m sure this will be fixed up in a future release of the power tools. All round I quite like the TFPT and haven’t even bothered with the multitude of 3rd party components that put a UI in front of it: real men use command line!
