TFS 2010: How to actually destroy orphaned data in DB

Question

Hi everybody,

I am working with TFS 2010 SP1 (in charge of its administration tasks). I found that one ProjectCollection has on file taking half of the global db files. After investigation, this file was deleted and needed to be destroyed. So I did a tf destroy on it, but forgot to put /startcleanup in command line. 

The issue is that destroy did not get any error, but tbl_file and tbl_content data is still there (but a call of tf destroy on this file tell me the file does not exist anymore, which is logic as long as i destroyed it).

My first reaction was: "ok, i forgot /startcleanup option, lets wait for the cleanup job to do it". So I waited, but it was last week, and data is still there...

What could cause this cleanup failure?

I have one big file as a sample, but seems to be more general on destroyed files.

Thank you for your help :).

Answer 1

Hi Gcourtin, 

Thanks for your post.

If not specify /startcleanup in Tf destroy command line, the destroyed content clean-up process occurs when the database maintenance cleans up all the files that are no longer referenced by VS TFS. By default, the clean-up is scheduled to run once each day.

Please check SQL Log for cleanup job, if the cleanup job failed on that day when you executed tf destroy command, maybe it will cause this issue. Please refer to this similar post: http://social.msdn.microsoft.com/forums/en-us/tfsversioncontrol/thread/5F3F8916-1C6D-46F7-9DAE-2CDAEAEE98DB.

If the cleanup process job failed caused this issue, and running the cleanup process manually still can’t remove that orphaned data, I suggest you to contact a Professional Support Service at http://support.microsoft.com/common/international.aspx?RDPATH=gp;en-us;offerprophone to gain more support on this case.   

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi John,

Thanks for your answer.

I allready found the post you linked. It's about TFS 2008 and i'm using 2010 version (the post refers to a KB but explicitly specify that it's for 2008 only).

As long as i'm using TFS 2010, there is no explicit log in database (because there is no more SQL jobs, but they are managed by Visual Studio Team Foundation Background Job Agent).

May be the root cause could be that tbl_file entries are  still there after the destroy?

I've quickly developped a console app to get errors for jobs in service. No significant error returned (3 errors due to smtp configuration changes on our network, but far before the destroy was done). Can anyone tell me which of this job is supposed to do the data cleanup?

Thanks for your help.

Job Build Scheduling
Job Common Structures Warehouse Sync
Job Build Resource Status Synchronization
Job Build Scheduling
Job Work Item Tracking Administration
Job Build Scheduling
Job Version Control Delta Processing
Job Build Administration
Job Build Scheduling
Job Work Item Tracking Identity Processing
Job Cleanup TestManagement Database
Job Work Item Tracking Integration Synchronization
Job Build Drop Deletion
Job Repopulate Dynamic Suites
Job Version Control Administration
Job Build Scheduling
Job Team Foundation Server Coverage Analysis
Job Synchronize Test Cases
Job Build Symbols Deletion
Job Version Control Statistics Update
Job Test Management Warehouse Sync
Job Security Identity Cleanup Job
Job Build Scheduling
Job Optimize Databases
Job Integration Administration
Job Build Warehouse Sync
Job Version Control Warehouse Sync
Job Version Control Code Churn
Job Job History Cleanup Job
Job Team Foundation Server Event Processing
Last Execution: from 20/03/2012 10:26:30 to 20/03/2012 10:31:34
Result: Failed
Message: There were errors or warnings during notification delivery.
0/3 emails delivered.
0/0 soap notifications delivered. 
3 errors.
0 warnings.
Set /Service/Integration/Settings/NotificationJobLogLevel in the TF registry to 2 to see errors and warnings.
Last Execution: from 20/03/2012 14:03:26 to 20/03/2012 14:03:28
Result: Failed
Message: There were errors or warnings during notification delivery.
0/1 emails delivered.
0/0 soap notifications delivered. 
1 errors.
0 warnings.
Set /Service/Integration/Settings/NotificationJobLogLevel in the TF registry to 2 to see errors and warnings.
Last Execution: from 21/03/2012 15:05:42 to 21/03/2012 15:05:44
Result: Failed
Message: There were errors or warnings during notification delivery.
0/2 emails delivered.
0/0 soap notifications delivered. 
2 errors.
0 warnings.
Set /Service/Integration/Settings/NotificationJobLogLevel in the TF registry to 2 to see errors and warnings.
Job Work Item Tracking Warehouse Sync
Job Team Foundation Server Activity Logging Administration
Job Team Foundation Server Framework Data Cleanup
Job Build Scheduling

Answer 3

Hi Gcourtin, 

Thanks for your reply.  

I think the Job Team Foundation Server Framework Data Cleanup job to delete the source control file.

I’m currently looking into this issue and will provide the better response ASAP.  

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi Gcourtin, 

How you ensure that files still existed TFS database? Check files’ ID in tbl_file and tbl_content?  

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us

Answer 5

Hello,

To check, I connect to SQL server database of the project collection and play the following request. It still returns data about the file I destroyed (quite simple to check for me because it's the bigger file in the project).

select p.ProjectName, vi.itemid, vi.ParentPath, vi.ChildItem, f.FileId,f.FileLength, f.compressedlength, count(c.FileId)
from tbl_project p
inner join tbl_versioneditem vi on substring(vi.ParentPath,3, len(p.ProjectName))=REPLACE(p.ProjectName,'_','>')
inner join tbl_File f on f.itemid=vi.itemid
inner join tbl_content c on c.FileId=f.fileId
where p.ProjectName='MytfsProjectName'
group by p.ProjectName, vi.itemid, vi.ParentPath, vi.ChildItem, f.FileId,f.FileLength, f.compressedlength
order by f.compressedlength desc

So the file is still existing in tbl_versionneditem, tbl_file and tbl_content... even after a destoy on it.

Thanks for your help.

Answer 6

Hi Gcourtin, 

Thanks for your reply.

If you can ensure that destroyed file still existed in TFS database, I suggest you to contact a Professional Support Service at http://support.microsoft.com/common/international.aspx?RDPATH=gp;en-us;offerprophone to gain more support on this case.  

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us

Answer 7

Hi all,

After Support assistance, the problem root cause was probably that I destroyed a file which were in a folder that was renamed and deleted. The history is not so simple but the result is that the path I used to destroy was not the right one (even if destroy returned a success).

To solve the problem, we undeleted the file in the renamed folder, and destroyed it with a /startcleanup. It Solved the issue.

It stays quite weird to me, I added some steps on my "destroy a file" procedure :).