Sql-server – Why does SSAS keep restarting every few seconds

sql serverssas

We have a SQL Server 2005 Analysis Server which began restarting this weekend, seemingly without any external changes.

The current state of the server is that as long as the SSAS service is enabled, it will keep restarting. If the service is manually disabled and then stopped, the service will no longer cycle. But as soon as it is enabled, the service will begin to cycle again.

Internet searching finds a few examples of this behavior, but without a resolution which seems applicable. See, e.g.: Service Level Accounts – SQL 2005 SSAS – Error connecting to OLAP. See also, e.g.: I Can’t Connect to Analysis Services in SSMS…

Brent Ozar reported a similar problem on LessThanDot, but there was no resolution on the thread and the last entry was at Fri Mar 27, 2009 4:08 pm.

This is a snippet of the msmdsrv.log file:

(10/6/2014 3:21:43 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:21:43 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
(10/6/2014 3:22:40 PM) Message: Service stopped. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)
(10/6/2014 3:22:45 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:22:45 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
(10/6/2014 3:23:41 PM) Message: Service stopped. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)
(10/6/2014 3:23:47 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:23:47 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)
(10/6/2014 3:24:41 PM) Message: Service stopped. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210001)
(10/6/2014 3:24:47 PM) Message: The flight recorder was started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210005)
(10/6/2014 3:24:47 PM) Message: Service started. (Source: \\?\S:\CubeLog\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x41210000)

The event viewer for system log, over and over, has:

"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a start control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a start control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a start control."
"The SQL Server Analysis Services (MSSQLSERVER) service was successfully sent a stop control."
etc....

There are no SQLDmprNNNN.mdmp files which seem to be created. There is no other error logging that we are aware of.

How can we find out what's wrong? Is there a way we can tell if any third party services or processes are cycling the SSAS service?

We have also posted this question on SQL Server Central.

Best Answer

enter image description hereWe never found out what was causing the service to stop, but we were able to prevent it from happening. We put a Deny on the Stop permission for SSAS service for the SQL Server (not SSAS) service account user.

So it seems something acting as the SQL Server's service account user was issuing the Stop command for the SSAS account. This was a while ago, and we've since switched servers, and we stopped digging to figure out what was really happening. So it will remain a mystery for the ages.

Related Question