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.
After a system failure and reboot of some kind, mostly 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)
- Errors in the metadata manager. An error occurred when loading the Team System cube, from the file, ‘C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\Tfs_Analysis.0.db\Team System.25.cub.xml’. Errors in the metadata manager. An error occurred when loading the Test Suite dimension, from the file, ‘\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\Tfs_Analysis.0.db\Test Suite.12.dim.xml’. File system error: The following file is corrupted: Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\Tfs_Analysis.0.db\Test Suite.0.dim\12.Hierarchy.Team Project.lstore. Logical file .
- Login to the TFS SQL machine (e.g. rdtfssql01)
- 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:
- From Windows Explorer:
- Go to the following location:
“%ProgramFiles%\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data”
- Go to the following location:
- Rename (or delete) the folder "TFS_Analysis.n.db", where ‘n’ is a number
- Rename (or delete) the file TFS_Analysis.n.db.xml, where ‘n’ is a number
- Start “SQL Server Analysis Services” by choosing "Start" instead of "Stop" per step 1.
- Login to the TFS Server machine (e.g. rdtfs01)
- Open the “TFS Administration Console”,
- Go to the “Reporting” node under "Application Tier"
- Click “Edit”
- Type in the correct username and password for the TFS Service account (e.g. SWG\svc_rdtfs). Contact IT if you need the password
- Rebuild the report and analysis databases.
- Load a browser and go to: http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx
- Click "Process Warehouse"
- Click the "Invoke" button
- This operation may take a few hours. You can monitor the status in two ways:
- At the same URL, http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx, click "GetProcessingStatus". Click "Invoke". The warehouse processing is done when the job goes idle.
- Alternately, you can check status of the rebuild in the TFS Admin Console, waiting for the "Status" field to show "Idle":