TFS: Work items delete

As a TFS/Collection administrator I come across scenarios to improve overall health of the TFS environment.

 

One common scenario is cleaning up data. Periodically I will clean up data to makes sure the TFS collection is in a manageable/healthy state. This also helps to improve overall TFS performance and reduced sync latency in processes like warehouse sync adaptors.

 

Below are SQL queries to find list of work items (List may vary based on the filter condition, change where clause based on business required):

 

For TFS 2013/2012:

SELECT ID FROM [WorkItemsAre] WITH (NOLOCK)

WHERE [Created Date] < DATEADD(MONTH, -18, GETUTCDATE())

 

You may also consider deleting work items based on last updated/changed date. Below is the query to get the work item ID's:

SELECT ID FROM [WorkItemsAre] WITH (NOLOCK)

WHERE [Changed Date] < DATEADD(MONTH, -18, GETUTCDATE())

 

For TFS 2015:

SELECT ID FROM [tbl_WorkItemCoreLatest] WITH (NOLOCK)

WHERE [CreatedDate] < DATEADD(MONTH, -18, GETUTCDATE())

  

Note: Work item destroy command used in the PowerShell script below is non recoverable command. Take required database backups if work items may need to be accessed in future.

 

During the work item destroy operation Team Foundation Server warehouse database will not be updated. Based on the amount of data deleted warehouse may show data latency for hours until warehouse adaptor will catch with all the updates.

 

Copy and paste below script in a PowerShell file (with .ps1 extension), update the variable values mentioned in the list #4 below and run the command from a machine where witadmin tool is installed (Generally available after visual studio installation). Open PowerShell command window and execute the script.

 

Note: Account running below script should have team foundation administrator or collection administrator access.

 

To delete large number of records you may also run multiple instances of below script. I have not seen any issues while running 2 instances of the script to delete 300K records.

 

########TFS Work Items Bulk Destroy Automation Script##########

#Notes:

#1) This script requires to setup file/folder path, validate the file/folders path before running the script

#2) start the powershell window as Administrator and run the script

#3) This script requires share and admin access on the destination server, make sure your account or the account under which script is executing is member of admin group

#on the destination server

#4) Update following:

# 4.1: $CollectionURL

# 4.2: $WitAdmin tool location

        # For VS 2015, Default location is C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE

        # For VS 2013, Default location is C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE

# 4.3: $WI_List

# 4.4: $logfile

####################

 

$CollectionURL = "https://tfs:8080/tfs/CollectionName"

$WitAdminLocation = "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE"

$WI_List = Get-Content "C:\WI_List.txt"

$logfile="C:\log.txt"

$ExecutionStartTime = Get-Date

$WICount = 0

"## Starting WI Destroy @ $ExecutionStartTime ##"| Out-File $logfile -Append

"Collection URL: $CollectionURL" | Out-File $logfile -Append

foreach ($WIID in $WI_List)

    {

        CD $WitAdminLocation

        .\witadmin destroywi /collection:$CollectionURL /id:$WIID /noprompt

        "WI ID: $WIID Destroyed" | Out-File $logfile -Append

        $WICount = $WICount + 1

        Write-Host "$WICount Work Items Deleted"

    }

 

$ExecutionEndTime = Get-Date

"## WI Destroy Command Completed @ $ExecutionEndTime ##"| Out-File $logfile -Append

 

$TotalExecutionTime = $ExecutionEndTime - $ExecutionStartTime

 

"Total Work Items Deleted: $WICount"   | Out-File $logfile -Append

 

" Total Execution Time: $TotalExecutionTime"  | Out-File $logfile -Append

 

##End of script##

 

Other References:

witadmin destroywi command details here

TFS permission references here