Wednesday, October 7, 2015

Error - Analysis Services Failed To Start in SQL Server 2012

Hi Guys,

Read my last blog on Error while configuring SQL Server Cluster here. As a DBA, we cannot or say we should not limit our self to only Database Engine related stuff. Today let's learn something related to Analysis Service i.e. SSAS.

In one of the migration project, I wanted to migrate an SSAS Database which is called as "Cube" to another Server.

So firstly, I needed the backup of the Cube. See the following command to take the backup of the Cube.
SSAS Cube Backup Code
SSAS Cube Backup Code
Where Test is the Database Name and Test.abf is the Backup Name (Which will take the Backup in the default location).

Now, I wanted to restore this backup to my new Server. But when I tried to connect the SSAS, it got  failed due to SSAS was disabled. So tried to start the SSAS and it gave the following error:

SQL Server Analysis Services Stopped

And when I checked Windows Event Viewer it captured the below error message:

Windows Event Viewer Error
Windows Event Viewer
From the Error in the Event Viewer it is confirmed that it is related to something with permission issue. So what we commonly do is "Going to that particular drive\ Folder and give full right to the account which runs the SQL Server Analysis Services". But unfortunately it din't helped.

I started searching more in google related to this error and in most of the sites they will provide the same solution which we saw above. And the search continued and continued till I landed to the next solution. And solution was to check the msmdsrv.ini file (It is an Configuration file for AS).

Basically when we configure AS, we have to pass a location where Data and Log file will create, but in my case no such files where created. So what did was as follows:

1. Created two Folder's Data & Log on E:\ and F:\ drive respectively and gave full permission to it.

2. Open the msmdsrc.ini file (in notepad) from this location (default):

C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config

msmdsrv.ini
msmdsrv.ini File
3. As we can see DataDir and LogDir tags which contains the location for the files; Change the path save it.

4. Then tried to re-start the AS and it was successfully started.

So the resolution for this seems simple. Want to know what is SQL Server Azure read my introduction post on Azure. Do comment if you like of dislike the page.

Thank You !!!
Keep Learning and Enjoy Learning !!!