TFS 2010 Data warehouse does not populate after rebuilding.

Question

Last Friday, our data-tier was moved from a single server (DB Engine, Analysis and Reporting running from same box) to the database cluster for the DB Engine and a virtual setup for Analysis and Reporting. Reconfiguring reporting on the original TFSReporting account continued to produce errors, indicating that not all information about the accounts was provided for the Analysis and Reporting tabs. I suspected the problem to be the account being too long (DOMAIN\SRV_WEBSVCS_TFSREPORTS, which would be truncated to DOMAIN\SRV_WEBSVCS_TFSREPOR), but can’t rule out a password mismatch or other reason. Anyway, another (shorter named) account was used with a verified password and configuration for reporting was completed successfully. Also, the new account was given db_owner role membership in all TFS databases. Next the rebuild was started, it had the entire weekend to complete, but not much happened. Only three tables in the data warehouse contain data; dbo._PropertyBag (2 rows), dbo._WarehouseConfig (21 rows) and dbo._WarehouseTimeOffsets (1002 rows). Rebuilding the warehouse was tried again after the weekend (had to clear the table dbo.tbl_JobQueue in configuration database, as it blocks the rebuild), but it doesn’t go beyond the situation above.

What are the options to troubleshoot why the data warehouse isn’t populated?

TFS version: 10.0.40219.1
SQL Server old: 10.50.1600
SQL Servers new: 10.50.2500

 

Answer 1

 

Stan Segers,

Did you rebuild both TFS_Warehouse and TFS_Analysis?

If yes, Could see the job status using GetProcessingStatus method on this TFS Webservice on the TFS App Tier?

http://localhost:8080/ VirtualDirectory /TeamFoundation/Administration/v3.0/WarehouseControlService.asmx

More detail about this webservice is available at http://msdn.microsoft.com/en-us/library/ff400237.aspx

This GetProcessingStatus would provide whether the job is still running or failed and it would also show the error message as well.

---

Regards,
Adhi
My TFS Blog
Please remember to mark as answered, if this reply helps

Answer 2

Both TFS_Warehouse and TFS_Analysis were rebuilt (at least, the command was issued).

Output from GetProcessingStatus:

<?xml version="1.0" encoding="utf-8" ?> 
<WarehouseProcessingStatus xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/TeamFoundation/2005/06/Services/Controller/03">
<RequestTimeUtc>2012-04-03T06:46:28.3181018Z</RequestTimeUtc> 
  <WarehouseProcessingOnlineStatus>Stopped</WarehouseProcessingOnlineStatus> 
  <AnalysisProcessingOnlineStatus>Stopped</AnalysisProcessingOnlineStatus> 
<JobProcessingStatus>Idle</JobProcessingStatus> 
<JobsRunning>0</JobsRunning> 
<JobsQueued>0</JobsQueued> 
<Instance Name="TEAM FOUNDATION" JobProcessingStatus="Idle" JobsRunning="0" JobsQueued="0">
<Jobs>
<Job Name="Common Structures Warehouse Sync" JobProcessingStatus="Idle" /> 
<Job Name="Full Analysis Database Sync" JobProcessingStatus="Idle" /> 
<Job Name="Incremental Analysis Database Sync" JobProcessingStatus="Idle" /> 
</Jobs>
</Instance>
<Collections>
<Collection Name="MainCollection" JobProcessingStatus="Idle" JobsRunning="0" JobsQueued="0">
<Jobs>
<Job Name="Build Warehouse Sync" JobProcessingStatus="Idle" /> 
<Job Name="Common Structures Warehouse Sync" JobProcessingStatus="Idle" /> 
<Job Name="Test Management Warehouse Sync" JobProcessingStatus="Idle" /> 
<Job Name="Version Control Warehouse Sync" JobProcessingStatus="Idle" /> 
<Job Name="Work Item Tracking Warehouse Sync" JobProcessingStatus="Idle" /> 
</Jobs>
</Collection>
<Collection Name="LegacyCollection" JobProcessingStatus="Idle" JobsRunning="0" JobsQueued="0">
<Jobs>
<Job Name="Build Warehouse Sync" JobProcessingStatus="Idle" /> 
<Job Name="Common Structures Warehouse Sync" JobProcessingStatus="Idle" /> 
<Job Name="Test Management Warehouse Sync" JobProcessingStatus="Idle" /> 
<Job Name="Version Control Warehouse Sync" JobProcessingStatus="Idle" /> 
<Job Name="Work Item Tracking Warehouse Sync" JobProcessingStatus="Idle" /> 
</Jobs>
</Collection>
</Collections>
</WarehouseProcessingStatus>

<SideNote>In a previous attempt the jobs were started, which resulted in 10 jobs running for the TFS_Warehouse and 3 jobs running for TFS_Analysis shown in the Report tab of TFS Admin Console. However, no data was added to the warehouse over the weekend. The rebuild was reissued (though we had to empty the [TFS_Configuration].[dbo].[tbl_JobQueue first]).</EndSideNote>

Following the documentation you pointed me to, invoking the ProcessWarehouse would be the next step. Invoking this methode results in a HTTP 500 error (application pools in IIS are all up).

Answer 3

Stan Segers,

It seems the job online status is stopped, it should be “Started”.

<WarehouseProcessingOnlineStatus>Stopped</WarehouseProcessingOnlineStatus> 
<AnalysisProcessingOnlineStatus>Stopped</AnalysisProcessingOnlineStatus>

Could you check the jobs are enabled and running?

You could also bring the Analysis and Warehouse processing online by invoking “BringAnalysisProcessingOnline” and “BringWareshouseProcessingOnline” methods on WarehouseControlWebservice web service

Or Clicking “Start Jobs” on Team Foundation Server Admin Console”

---

Regards,
Adhi
My TFS Blog
Please remember to mark as answered, if this reply helps

Answer 4

Hello Adhithan,

Jobs started, this resulted in the following ProccesingStatus;

<?xml version="1.0" encoding="utf-8" ?> 
<WarehouseProcessingStatus xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/TeamFoundation/2005/06/Services/Controller/03">
<RequestTimeUtc>2012-04-03T15:22:50.1696734Z</RequestTimeUtc> 
<WarehouseProcessingOnlineStatus>Started</WarehouseProcessingOnlineStatus> 
<AnalysisProcessingOnlineStatus>Started</AnalysisProcessingOnlineStatus> 
<JobProcessingStatus>Idle</JobProcessingStatus> 
<JobsRunning>0</JobsRunning> 
<JobsQueued>0</JobsQueued> 
<Instance Name="TEAM FOUNDATION" JobProcessingStatus="Idle" JobsRunning="0" JobsQueued="0">
<Jobs>
<Job Name="Common Structures Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.16Z" JobState="QueuedScheduled" /> 
</Job>
<Job Name="Full Analysis Database Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-04T00:00:00Z" JobState="QueuedScheduled" /> 
</Job>
<Job Name="Incremental Analysis Database Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T17:12:04.52Z" JobState="QueuedScheduled" /> 
</Job>
</Jobs>
</Instance>
<Collections>
<Collection Name="MainCollection" JobProcessingStatus="Idle" JobsRunning="0" JobsQueued="0">
<Jobs>
<Job Name="Build Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.157Z" JobState="QueuedScheduled" /> 
</Job>
<Job Name="Common Structures Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.157Z" JobState="QueuedScheduled" /> 
</Job>
<Job Name="Test Management Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.157Z" JobState="QueuedScheduled" /> 
</Job>
<Job Name="Version Control Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.157Z" JobState="QueuedScheduled" /> 
</Job>
<Job Name="Work Item Tracking Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.157Z" JobState="QueuedScheduled" /> 
</Job>
</Jobs>
</Collection>
<Collection Name="LegacyCollection" JobProcessingStatus="Idle" JobsRunning="0" JobsQueued="0">
<Jobs>
<Job Name="Build Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.15Z" JobState="QueuedScheduled" /> 
</Job>
<Job Name="Common Structures Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.15Z" JobState="QueuedScheduled" /> 
</Job>
<Job Name="Test Management Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.15Z" JobState="QueuedScheduled" /> 
</Job>
<Job Name="Version Control Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.15Z" JobState="QueuedScheduled" /> 
</Job>
<Job Name="Work Item Tracking Warehouse Sync" JobProcessingStatus="Idle">
<NextRun QueueTimeUtc="2012-04-03T15:22:59.15Z" JobState="QueuedScheduled" /> 
</Job>
</Jobs>
</Collection>
</Collections>
</WarehouseProcessingStatus>

Data in datawarehouse remains at; dbo._PropertyBag (2 rows), dbo._WarehouseConfig (21 rows) and dbo._WarehouseTimeOffsets (1002 rows).

Regards,

Stan

Answer 5

Stan,

Ok. So all you see is only these 3 tables (dbo._PropertyBag (2 rows), dbo._WarehouseConfig (21 rows) and dbo._WarehouseTimeOffsets (1002 rows)) in TFS_Warehouse database, is that correct?

When you moved your database did you run this command to remap the dbs?

TFSConfig RemapDBs /DatabaseName: ServerName ; TFS_Configuration /SQLInstances: ServerName,ServerName2 /AnalysisInstance:ServerName2 /AnalysisDatabaseName:DatabaseName

If not, you might need to do that, you can also refer this link more detail http://msdn.microsoft.com/en-us/library/ms404869(v=vs.100).aspx

---

Regards,
Adhi
My TFS Blog
Please remember to mark as answered, if this reply helps

Answer 6

Hello Adhithan,

Yes, you are correct on the rowcount for the TFS_Warehouse.

Also the TSConfig RemapDBs was executed as part of the move. The actual documentation guideline used for the move was: http://msdn.microsoft.com/en-us/library/ms252516.aspx. At alater stage, a full rebuild was issued because processing did not turn up results and the reporting tab of TFS Admin Console reported schema mismatches (can't remember the exact wording of the message).

Regards,

Stan

Answer 7

This one is to stupid to be true; it turns out that the TFSJobAgent service wasn't running. The fact that daily builds weren't running, but interactive builds did run, was our trigger to check for this. We examined the eventlogs and found no indication that the TFSJobAgent service failed to start after a reboot, so it must be that during troubleshooting a TFSServiceControl quiesce wasn't followed by a TFSServiceControl unquiesce.