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: https://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: https://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 https://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:
https://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.