Today my SSAS server died and I lost 500 DBs but I still have all DB files

Question

All of a sudden, today after an update of Symantec Antivirus on the server (no relation, this was just the most recent change event), my poor SSAS server with 500 databases refused to start.

The config of the server is: 10 CPUs, 512GB RAM, Windows 2016, SQL 2016 Enterprise. Yes, it is old, but it is part of a legacy solution where unfortunately nothing can be done. And yes, it used to work without major issues until today.

SSAS did not indicate any error in the event log, msmdsrv.log or Flight Recorder. SSAS service simply stuck in Starting / Change pending mode. The memory used by the msmdsrv process did not increase and froze at the level of 104Mb for hours, while normally when the service is fully started (which takes about 20-30 minutes), it uses at least 240GB RAM.

I tried the following things:

1. Force-stop msmdsrv process and restart the SSAS service -> no success, SSAS service still stuck at Starting.
2. Restart the entire server and restart the SSAS service -> no success, same as above.
3. Change DataDir from the old folder with 500 SSAS DBs to a new empty folder, let's call it E:\SSAS\ -> SSAS service did start successfully, but obviously there were no DBs.
4. I tried to copy a few DB folders together with their .db.xml files from the old location to E:\SSAS\ and restarted the server -> SSAS DELETED the db folders together with the xmls at the start.

I still have a few backups of older databases, but not recent ones. So I cannot restore the data. Ideally I would just go detach-attach, but as I cannot connect to the server, there is no way to detach.

Of course I still have the old DataDir folder with the original 500 SSAS DB folders and xmls, but looks like it's good for nothing, because I cannot simply copy the old SSAS DB folder to the new location - SSAS deletes the folder and the xml at the start.

Do you have any idea what to do?

Answer 1

Hi @iz ,

Welcome to Microsoft Q&A!

Antivirus locking AS data files can cause a hiccup if the file cannot be opened in a timely manner when it is needed. Please get more information from How to choose antivirus software to run on computers that are running SQL Server. And also, hardware related problem on the SSAS server can cause damage to SSAS database such as disk write problems.

SSAS did not indicate any error in the event log, msmdsrv.log or Flight Recorder. SSAS service simply stuck in Starting / Change pending mode.

Collect dump file (.mdmp) if any from OLAP\Log folder. If you did not find any error log, please try to use SQL Base Collector.

Here have some solutions you could try:

1. Try to restore the backup files to another server, and then ProcessFull database. Please noted that the roles will disappear after restore databases to another server.
2. If you have SSAS project, try to redeploy it, and then ProcessFull.

Best regards,
Carrin

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I found out the root cause, and this was not the antivirus.

A few weeks ago, we tested the possibility to migrate an SSAS DB to a fileshare. It did not work generally, because of long paths. Only one test DB with short paths could have been migrated. We decided to not go for SSAS files on the fileshare. But the test DB remained on the fileshare.

The problem was that the fileshare had been deleted. This was not noticed during SSAS runtime, but after service restart, the process msmdsrv.exe was trying to find the DB files and failed.

SSAS did not produce any event log or msmdsrv.log entries. I was only able to track this behavior down by analyzing the process in procmon.

After I manually deleted the DB directory in DataDir, SSAS started.