How to: Delete Orphaned Files Permanently
You might want to permanently delete files that become orphaned when a client user deletes an attachment from a work item. For example, a work item might have an attached file that is corrupted or has a virus.
Deleting the file removes it from the work item and the project. The databases still contain the rows for the metadata and content. However, the RemovedDate is changed to the current date and time and the file becomes orphaned. As the database administrator, you can delete the attachment permanently.
The content of the file is stored in the TfsWorkItemTrackingAttachment database as a single entry for the file. This entry includes a GUID and the content as an image.
Metadata for the file is stored in the WorkItemFiles table of the TfsWorkItemTracking database. The metadata includes the ID number for the work item.
To perform this procedure, you must be a member of the SQL Server Administrator group or have database administration permissions to issue DELETE commands. A file infected with a virus is unavailable after removal from the team project.
To delete an orphaned work item attachment
In Team Explorer, open the work item and delete the file from the File tab.
Either the client user or an administrator can delete the file and make it an orphan in the database.
Log on to the data-tier server as a user who has database administration permissions.
Click Start, click All Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.
In the Connect to Server dialog box, select the server, and then click Connect.
If the name of the server is not in the dialog box, you can type the name directly into the box.
Run a query in the TfsWorkItemTracking database to obtain a list of attachments for the work item.
For example, you might use this query that returns items with attachments as indicated by the FldID value 50 and the ID for the work item:
USE TfsWorkItemTracking GO SELECT * FROM WorkItemFiles WHERE FldID=50 AND ID = 15
In the OriginalName column in the query results, locate the file name, and note the GUID found in the FilePath column.
In the TfsWorkItemAttachment database, issue a Delete query that is based on the GUID.
You cannot undo the action of the Delete query. As a precaution, back up your data before running a Delete query.
USE TfsWorkItemAttachment GO DELETE FROM Attachments WHERE FileGuid=<<GUID>>