I had seen few scenario where you perform any action on Analysis Services you get weird Error Message like some store is corrupted
An error occurred when instantiating a metadata object from the file, '\\?\J:\Program Files\Microsoft SQL Server\MSAS10.SQLInst\OLAP\Data\dbDev.0.db\testdim.2.dim\DimensionPermission 1.0.perm.xml'.
To resolved Database Corruption follow below mentioned steps:
- Take the backup of Data Directory (your choice)
- Create XMLA of All Database (Script as Database in a File)
- Stop Services
- Rename Existing Data Director to OLD
- Created New Empty Data Dir
- Start Analysis Services
- Using XMLA of database created new DB
- Process Database
- Take the backup of Data Directory
- Stop Analysis Services
- Create New Empty Data Dir
- Deploy Database from BIDS Project & Process Them
- Restore Database from Good Version of Backup (.abf)
- Restore Database from Good Version of Disk Level Backup (Ensure that whole data folder is copied)
*Partial copy of Data Folder or replacing few files in Data Folder is not Supported by Microsoft Support
Once you are Sure that Database are recovered and you are able to browse your Data (done complete testing) then feel free to drop old Corrupted Data Folder
Here are the possible causes of Corruption, though there is no definitive list of corruption:
- If AS Services restarted during Processing of Cube.
- Sometimes Antivirus / Monitoring Tools locks file and if services restarted during that state it can cause corruption.
- SSAS Data Folder goes out of Disk Space while doing Processing
Ignore Corruption - This may help in few cases but not in all
While doing any maintenance task on Server Like Patching / Server Updates or Restart ensure that no user is connected to Analysis Services, this you can ensure by checking profiler traces.
Ensure that SSAS Folders are excluded from any utility which does File Level Scan like Anti Virus, Disk Monitoring Tools etc.
Sufficient free space in SSAS Data Drive
(In case of Corruption Recommendation - Involve Microsoft Support Services)
That’s quite useful, thanks for the information.
I have come across with a scenario where I don’t see my Cube DB at all and I was in an impression that it might have been dropped by someone but I also learnt that, “A database can be dropped by SSAS at start up in case of cube corruption”. I am curious to understand is there anyway I can track or check in Logs that Cube got dropped by SSAS.
Please Help !!!
That could happen if somehow your master.vmp file doesnt have entry for associated database.
Regd - Logs - unfortuantely there is no mechanism of tracking corruption
What i can advise you is to go - connect.microsoft.com and give your feedback regd. this to our Product team.
U can also restore from good backup.
try to move temp file to drive which has enough disk space.
You can validate the current path in Config file named "msmdsrv.ini" located at
Drive:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Config
hope this help!
Alpesh Dhori | Software Professional
In workaround 1, what do you mean by "Using XMLA of database created new DB"? I am very new to this and am experiencing the corruption issue but am unfamiliar with most of these steps.
after renaming Data folder , cannot start analysis service , it's says some bin file is missing.
do you have any Idea or suggestion ??
Thank God for you. I have spent 4hrs on Christmas Day trying to recover from an unplanned system reboot and a (Suspect) Analysis Services database. I hadn't restoring database did not work until I used a new Data directory.