Sunday, December 11, 2011

My SSAS databases are corrupted

Did you happen to have a unplanned shutdown?  This is quite likely to be the cause of the failure.  This is unlike SQL Server, which has a transaction log and will always recover databases on restart.
As I have mentioned earlier, SQL Server Analysis Services exploits the Windows File Cache to help minimise IO, which might lead you to suspect the Windows cache.  However SSAS uses the FlushFileBuffers API to minimise the possibility of having dirty pages in the Windows File Cache.  However, this does not protect SSAS from corruption on an unplanned shutdown.
So, if you really want to avoid corrupted SSAS databases on power failure, you should ensure your SSAS servers are supported by UPS.
Most production SSAS servers have UPS.  But, if you don't have UPS, like my demonstration site ( and, like my site, the cubes are continually updating, when the power fails, SSAS databases have a good chance of being corrupted.  The only solution to this, that I know of, is to restore the affected (typically all) SSAS databases.  I find the quickest way to do this is to stop SSAS, empty the SSAS data directory, start SSAS and recover all the databases.  One thing you will also need to do is add back any userids that had SSAS administrator privileges as they were held in a file on the SSAS data directory.

No comments: