Warehouse rebuild is one of the common practices during upgrades. Recently, we upgraded one of our customers from TFS 2010 à TFS 2012 and it wasn't an inplace upgrade. New DB environment is ready, detached current DB backups have been moved to the new server and upgrade takes place. Collection DBs have been upgraded. Now, we are at a point to rebuild Warehouse and Analysis database. After proper configurations in the TFS Admin console, we kicked off Rebuild process from Reporting node of TFS admin console.
Until this point, everything was reasonably quiet. However, the rebuild process was taking longer than expected. Waited for more than 10 hours and it seems to be making progress but we aren't quite sure where its and how long its going to need.
We can track the processing status by accessing WarehouseControlService as shown below..
'Work Item Tracking Warehouse Sync' was in progress and there were no hints how much is done in last 10 hours and how much to go…
The only clue we had was the collection database size.. it was around 2.2 Gig and that indicated a little bit but that wasn't enough to answer our questions…its been 10+ hours, should we continue or abort the process and see if something is wrong…no idea..
Here is a theory that helped me and hope that would help you too, if your symptoms are similar
Lets consider a sample here…I got 98 work items in my collection
Getting the same numbers from DB as well
However, that doesn't mean we got 98 work items including the historical transactions.
As you may agree, that's not all.. in TFS data flow thru 3 stages (Collection DB à Warehouse à SSAS) for every transaction..
so where are the transactional history that needs to be synchronized from Collection DB à Warehouse.. below is query
Hope you see where I'm going.. for that 2Gig DB, there were little over 2 Mil transactions to be processed ...so that answers how much to be processed? – 2+ Mil records
So, I'm making a few sample transactions here to see the difference
With the sample transactions..how many transactions are waiting to be processed (it went up to 290 from 258)
Next question, how much is done?
To see that we need to access Tfs_Warehouse database
This should increase and match the number that you get from Collection DB's WorkItemsAsOf count..
In my case, we were around 1 Mil and half way to go..that answers remaining questions..Once it's done, we need to process SSAS DB as usual.
So, incase you are hit with similar situation, try to analyze the issue and provide more resources for the SQL box to get your job done as fast as possible..
