Today my SSAS server died and I lost 500 DBs but I still have all DB files
-
The title sounds like a name to a new anime, and it's my daily life.
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:
- Force-stop msmdsrv process and restart the SSAS service -> no success, SSAS service still stuck at Starting.
- Restart the entire server and restart the SSAS service -> no success, same as above.
- 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.
- I tried to copy a few DB folders together with their .db.xml files 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?
-
Un-install Symantec anti-virus? Or make sure you have excluded anything related to this instance from being scanned?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply