How to Deal with Corruption in Analysis Services

I had seen few scenario where you perform any action on Analysis Services you get weird Error Message like some store is corrupted

Sample Error:

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:

Workaround 1

- 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

or

Workaround 2

- Take the backup of Data Directory

- Stop Analysis Services

- Rename Existing Data Director to OLD

- Create New Empty Data Dir

- Start Analysis Services

- Deploy Database from BIDS Project & Process Them

or

Workaround 3

- Take the backup of Data Directory

- Stop Analysis Services

- Rename Existing Data Director to OLD

- Create New Empty Data Dir

- Start Analysis Services

- Restore Database from Good Version of Backup (.abf)

or

Workaround 4

- Take the backup of Data Directory

- Stop Analysis Services

- Rename Existing Data Director to OLD

- Create New Empty Data Dir

- Start Analysis Services

- 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)

Comments

• Anonymous
  May 24, 2012
  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 !!!

• Anonymous
  May 24, 2012
  That could happen if somehow your master.vmp file doesnt have entry for associated database. blogs.msdn.com/.../tampering-master-vmp-file-may-result-in-losing-all-analysis-services-databases.aspx 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.

• Anonymous
  October 03, 2013
  U can also restore from good backup.

• Anonymous
  March 17, 2014
  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 FilesMicrosoft SQL ServerMSAS10_50.MSSQLSERVEROLAPConfig hope this help! Regards, Alpesh Dhori | Software Professional

• Anonymous
  July 15, 2014
  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. Thanks!

• Anonymous
  August 18, 2014
  after renaming Data folder , cannot start analysis service , it's says some bin file is missing. do you have any Idea or suggestion ??