Corrupt SSAS databases – a partial solution

Today (for the second time in my expereince) i had a VM go corrupt and lost the normal operation of my SSAS 2008 R2 instance. I noticed that only one database was showing up in SSMS but it wasnt working i.e. you could not query/browse the cube due to error dialogs popping up.

Strangely other SSAS databases were queryable (i.e. dashboards worked) – these were the ones NOT showing up in SSMS.

I ran a SQL Profiler trace and noticed the following when trying to start the SSAS service:

Errors in the metadata manager. The dimension with ID of ‘Dim Customer’, Name of ‘Customer’ referenced by the ‘Customer Clusters ~MC’ cube, does not exist. Errors in the metadata manager. An error occurred when loading the Customer Clusters ~MC cube, from the file, ‘\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\Adventure Works DW 2008R2.0.db\Customer Clusters ~MC.3.cub.xml’.

 

I looked at my data folder (as per error above) and it seemed like somehow a number of the XML files SSAS uses to stored definitions of objects were no longer named correctly.. the “~MC” didnt seem right.

Luckily for me it was the Adventureworks database that was corrupt. I needed access to the other databases which have my demo work on them. So i did the following:

  1. Stop SSAS service
  2. Browse to the Data folder
  3. Delete the following:
  • File: Adventure Works DW 2008R2.8.db.xml
  • Folder: Adventure Works DW 2008R2.0.db

This effectively removes the corrupt Adventureworks data files. Then i restarted SSAS service and was able to work as per usual.

So this is not the ideal outcome (i.e. cube lost) but it does allow your SSAS instance return to normal behaviour if one database is causing issues and you are unable to manage it with SSMS.

Advertisements

4 Responses to Corrupt SSAS databases – a partial solution

  1. Alistair says:

    Man! You just saved my DB! No idea how it happened, but one of my cubes suddenly had metadata problems. As a consequence I couldn’t see/access any of the other cubes on this server, or even manage the server itself. By deleting the cubes from through windows explorer I was able to restart the server and all the other cubes were available again. To get the deleted cubes back I had to deploy again, but that was no big deal. Thanks!

  2. Sakthi says:

    thank you.. this helped 🙂

  3. Mehul says:

    Thanks, you gave me an idea. I am using 2012 so the folder you mentioned did not exist. But when I deleted the whole folder after stopping the Analysis service my db was gone. I re-processed the cube and it recreated the db for me. Happy now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: