TFS 2010 Report Results are not up to date after installing Service Pack 1


Today I’m going to share some trouble shooting steps that I discovered while working on a case where a TFS 2010 was upgraded to Service Pack 1 which caused an issue with the warehouse processing so that the reports did show values however just to the time where SP 1 was installed.

Symptom:

When running any TFS report (like bug rates) no values showed up for events occurred after the service pack installation.

The general trouble shooting procedure for warehouse / reporting issues usually covers the steps necessary to diagnose the warehouse sync jobs.

These jobs are scheduled repeatedly so that work item transitions end up in the warehouse to be fetched by reporting services.

In order to check when these jobs were executed the last time and what error code they returned you may access the WarehouseControlService.asmx WebService.

See the MSDN Page for further Details: http://msdn.microsoft.com/en-us/library/ff400237.aspx#DetermineStatus

In my case one of the jobs kept failing with the error message:

TF221123: Job Work Item Tracking Warehouse Sync for team project collection DefaultCollection was was unable to run after 20 attempts

Cause:

First I guessed that the warehouse got out of sync for some reason and did not recover. So I asked the customer to rebuild the warehouse based on my earlier blob post: http://blogs.msdn.com/b/dau-blog/archive/2011/11/24/tfs-2010-warehouse-amp-reporting-trouble-shooting-basics.aspx

Unfortunately that did solve the issue and TF221123 remained the reported error.

Now I was sure the cause of the problem is not related to the warehouse processing but to some error within the relational databases of the project collections.

Further investigation revealed that under some circumstances the table WorkItemsLatest  may contain rows that have no corresponding rows in table WorkItemsAre which can be checked by running the following SQL queries against all project collection databases:

   1: select *
   2: from WorkItemsLatest
   3: where ID not in (select A.ID from WorkItemsAre A)
   4:      
   5: select *
   6: from WorkItemsAre
   7: where ID not in (select A.ID from WorkItemsLatest A)
   8:      
   9: select *
  10: from WorkItemsWere
  11: where ID not in (select A.ID from WorkItemsAre A)
  12:  
  13: select w.ID, w.Rev, l.ID, l.Rev 
  14: from WorkItemsAre w join WorkItemsLatest l on l.ID = w.ID 
  15: where l.Rev <> w.Rev 

If any of these SELECT statements returns rows, they indicate orphaned work items  and cause the warehouse sync jobs to fail.

Solution:

You can delete the invalid rows with a SQL DELETE statement as shown below:

   1: delete
   2: from WorkItemsLatest
   3: where ID not in (select A.ID from WorkItemsAre A)
   4:      
   5: delete
   6: from WorkItemsAre
   7: where ID not in (select A.ID from WorkItemsLatest A)
   8:      
   9: delete
  10: from WorkItemsWere
  11: where ID not in (select A.ID from WorkItemsAre A)
  12:  
  13: delete
  14: from WorkItemsAre w join WorkItemsLatest l on l.ID = w.ID 
  15: where l.Rev <> w.Rev 

Now, after re-processing the warehouse manually http://msdn.microsoft.com/en-us/library/ff400237.aspx

That helped to resolve the error above. However, now another error showed up in the processing status:

TF221122: An error occurred running job Build Warehouse Sync for team project collection or Team Foundation server DefaultCollection. ---> Microsoft.TeamFoundation.Framework.Server.DatabaseOperationTimeoutException: TF246018: The database operation exceeded the timeout limit and has been cancelled. Verify that the parameters of the operation are correct.

AI ran a quick search in our Knowledge Base and found a Hotfix released in September 2010 that didn’t sound promising “Builds in a gated check-in build definition incorrectly run concurrently and job fails in TFS 2010” but claimed to solve the exact error messages:

http://support.microsoft.com/kb/2567437/en-us

Bingo - after reprocessing the warehouse once again after installing the Hotfix, the warehouse process jobs succeeded and the bug report (and others) showed recent events.

Notice: When you encounter similar issues with your TFS and follow my trouble shooting attempt, please be aware that Hotfixes are tested against limited test cases and it is always wise to run full database backups before the installation or modification to the TFS databases!

Comments (0)

Skip to main content