A Tech Log

March 17, 2009

Delete Workitem in TFS (including the TFS data warehouse)

Filed under: Development — adallow @ 9:13 am
Tags:

taken directly from: http://bappedyanto.com/post/Delete-Work-Item-in-TFS.aspx

In TFS 2008, there’s a capability to delete unused work item. I know some people think that physically delete the work item is not needed because there are several work around to achieve it, nevertheless for the reason of tidiness everything, delete work item physically sometimes is required.

Out of the box, this feature is not available. You need to install TFS 2008 Power Tools and use the TFPT.EXE command line tools (although I believe GUI will be more than useful).

image

Be careful now, there’s no way to recover or undelete the work item by executing this tool.

As the description suggest, using this tool is not deleting records in datawarehouse (TFS uses SQL Server both OLTP and OLAP), only in operational database. The implication is the deleted work item will still appear in Team Reporting.

To fix this, manual deletion is required in TFS Datawarehouse database, some people will say that this practice is dangerous because potentially will break the data integrity but I think if we do it properly, the risk can be eliminated.

in SQL Management Studio, connect to TFS Database Server, and then unfold TFSWarehouse database.

image

Notice five tables with name related to work item. [Current Work Item], [Work Item], [Work Item Changeset], [Work Item History], and [Work Item with Result].

Query [Work Item] table to get the work item id in Datawarehouse with this SQL statement

SELECT __ID, System_Title FROM [Work Item] WHERE System_Id = workitemid

workitemid in here is the work items id that is used in TFPT command. Don’t get confused because in other 4 tables that previously noted the work item id that will be used is coming from [Work Item].__ID.

For the other 4 tables here’s the SQL statement that need to be executed

Delete [table name] where [work item] = workitem_from_[Work Item].__ID

Then rebuilt you cube by executing the OLAP Database and Warehouse Run from web services.

Disclaimer : please be very careful when conducting this procedure. I am not responsible if your TFS data is messed up

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: