How to fix Tfs_Analysis when it is corrupt.
Problem:
I was approached by my friend Nimrod Stern to help him with a problem he had regarding TFS reports.
When trying to open a TFS report the following error was received:
- An error has occurred during report processing. (rsProcessingAborted)
- 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 .
From the content of the error it seemed to me that the analysis DB was corrupt.
First thing I tried to do was to rebuild the reports databases from the TFS administration console (by going to the “Reports” tab and clicking “Start Rebuild”) and got this error:
“Error encountered when creating connection to Analysis Services. Contact your Team Foundation Server administrator.”
Later I tried rebuilding the reports databases from the web service (http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx). Unfortunately also this did not solve the problem.
Finely I resorted to try and rebuilding the Tfs_Analysis DB directly from the SQL management studio as such:
but it also did not work and produced this error:
“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 .
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'.
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'.
(Microsoft.AnalysisServices) “
(Looks familiar?? :) )
Solution:
Even though this problem cannot be solved with the “TFS Administration Consol” or the “SQL Management Studio” the solution is fairly simple:
1) Tern off the “SQL Server Analysis Services” windows service, either by right-clicking the analysis server in the “SQL Management Studio”:
or directly from Windows’ “Services” window:
2) Browse to this folder: “%ProgramFiles%\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data” and delete the folder Tfs_Analysis.X.db and the XML file Tfs_Analysis.XX.db.xml.
3) Restart the “SQL Server Analysis Services” windows service.
4) Open the “TFS Administration Consol” and go to the “Reports” tab, then press “Edit” and type in the correct username and password in the “Analysis” tab.
5) Rebuild the Report DB’s through the web service: http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx, or through the “TFS Administration Consol” - go to the “Reports” tab, then press “Start Rebuild”.
Wait for your DB’s to finish building, and enjoy your reports!