Although not a problem we see often, some of our clients run into issues with a corrupt TFS Analysis DB, particularly after an environmental failure of some kind. This doesn’t lead to any permanent damage or data loss but needs to be addressed. Here is a set of instructions we developed for this. Note that they are based on TFS 2010 where most of our clients are, but TFS 2012 is similar.

Problem

After a system failure and reboot of some kind, most likely a power outage, the TFS Analysis database, which is used for reporting via SQL Reporting Services and the SharePoint dashboards, may become corrupt and require a rebuild. Don’t worry – your data is not gone. The reporting and analysis databases are used to build up the historical information from the TFS project collection databases and display the appropriate metrics and charts.

The message indicative of this problem will be similar to the following when you navigate to the SharePoint dashboards and view one of the TFS web parts (e.g. http://<site>/sites/<collection>/<project>/Dashboards/ProjectDashboard_wss.aspx)

Query execution failed for dataset ‘dsIteration’. (rsErrorExecutingCommand)

Solution

  1. Login to the TFS SQL machine (e.g., rdtfssql01)
  2. On the SQL VM/machine that is supporting TFS and SharePoint, stop the “SQL Server Analysis Services” windows service, either by right-clicking the analysis server in the “SQL Management Studio” or directly from the services window:
  3. From Windows Explorer:
    1. Go to the following location:
      “%ProgramFiles%\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data”

NOTE: This file is likely on the data drive for the server! (E.g. E: drive)

  1. Rename (or delete) the folder “TFS_Analysis.n.db”, where ‘n’ is a number
  2. Rename (or delete) the file TFS_Analysis.n.db.xml, where ‘n’ is a number
  1. Start “SQL Server Analysis Services” by choosing “Start” instead of “Stop” per step 1.
  2. Login to the TFS Server machine (e.g., rdtfs01)
  3. Open the “TFS Administration Console,”
    1. Go to the “Reporting” node under “Application Tier.”
    2. Click “Edit”
    3. Type in the correct username and password for the TFS Service account (e.g., SWG\svc_rdtfs). Contact IT if you need the password
  1. Rebuild the report and analysis databases.
    1. Load a browser and go to http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx
    2. Click “Process Warehouse”
    3. Click the “Invoke” button
  1. This operation may take a few hours. You can monitor the status in two ways:
    1. At the same URL, http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx, click “GetProcessingStatus.” Click “Invoke.” Warehouse processing is done when the job goes idle.
  2. Alternately, you can check the status of the rebuild in the TFS Admin Console, waiting for the “Status” field to show “Idle”: