SQL SERVER – Error: 1067 – Unable to Bring Analysis Service Online in Cluster

Even after working with many clients on various issues (Always On, Deployment, Performance Tuning) I always get new challenges as a part of my freelancing job. One thing I have learned is that one needs a systematic approach to diagnose the issue and, of course, passion to solve the problem. In this blog, we would learn about how to fix the error: 1067 which comes while trying to bring Analysis Service online in the cluster.

Recently, one of my clients wanted me to fix an issue where they were not able to start SQL Analysis Services which was installed in a cluster. The major challenge here was that all the data available in the logs were very generic and what was surprising is that even the cluster logs had generic error messages. Refer below:

If you are new to cluster and wanted to know how to generate cluster log, then refer my earlier blog.

ERR [RES] Generic Service : Service failed during initialization. Error: 1067.
ERR [RHS] Online for resource Analysis Services (SSAS) failed.
WARN [RCM] HandleMonitorReply: ONLINERESOURCE for ‘Analysis Services (SSAS)’, gen(29) result 5018/0.
ERR [RCM] rcm::RcmResource::HandleFailure: (Analysis Services (SSAS))
WARN [RCM] Not failing over group Data Warehousing Instances, failoverCount 4, failoverThresholdSetting 4294967295, computedFailoverThreshold 1, lastFailover

From cluster log (ERR stands for error) we can see that we pretty much have a very generic error – “Error: 1067” which means “The process terminated unexpectedly”

SQL SERVER - Error: 1067 - Unable to Bring Analysis Service Online in Cluster ssas-clu-err-01-800x167

As I mentioned earlier, let’s start with the basic approach. We know this is a cluster and SSAS instance is a clustered instance. There are two ways we can confirm that the service is starting successfully.

  • Successfully starts as a clustered resource in the failover cluster manager
  • Successfully starts when we try to start it locally using Service Control Manager.

We know that we are failing with the first option. So, we went on to the second option.

  • We opened services.msc à right click on SSAS serviceà Click Start. We got the same error 1067.
  • Used NET START <servicename> method and that too failed with the same error.
  • Then we tried to start the service using the SSAS exe file exe.
  • We went to the properties of the SSAS service in Service Control Manager to get the path of the EXE and the startup parameters being used. And we saw the below values:

“D:\Program Files\Microsoft SQL Server\MSAS11.SSAS\OLAP\bin\msmdsrv.exe” -s “F:\OLAP\Config”

SOLUTION/WORKAROUND

As soon as my client saw the above data, he screamed I think, I know what the problem is” So, I asked him what is it? And he replied – “We don’t have F:\drive in this cluster!!”

He then explained the cluster storage re-shuffling they had done recently which may have introduced this issue. Now how do we fix this?

  1. Go back and rename the cluster drive letters to the original ones [this was unlikely to happen]
  2. Find another way to edit those values and we are talking about the registry here.

Every service installed in windows get their registration under,

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\<ServiceName>\

So, in this case we headed to,

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSAS$SSAS\ImagePath

Here we could see the value what we saw in services properties. We changed that to the below,

“D:\Program Files\Microsoft SQL Server\MSAS11.SSAS\OLAP\bin\msmdsrv.exe” -s “M:\OLAP\Config”

Then on we could start SSAS service locally from service control manager. And we were also able to start it from Failover Cluster Management.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – FIX: Error: 5511 – FILESTREAM’s file system log record under log folder is corrupted
Next Post
SQL SERVER – Be Careful with Logon Triggers – Don’t use Host_Name

Related Posts

Leave a Reply

Menu