1

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.

Improve this question
5
  • 1
    Worth a try to sysinternal utility - process monitor
    – Kin Shah
    Commented Oct 6, 2014 at 21:32
  • 1
    Did the OS get patched or anything? Might be worth a support call to MS if it's a prod instance
    – billinkc
    Commented Oct 6, 2014 at 21:36
  • @Kin - Thanks. What exactly would we monitor? How would it tell us what is restarting service?
    – Riley Major
    Commented Oct 7, 2014 at 14:37
  • @billinkc - It's a production instance but we have (much slower) backup instances which can be inconveniently used by folks, so the downtime is tolerated for the time being. We would normally just take aggressive measures (restarting, re-installing, etc.), but the box is also the production SQL Server, and there is much lower tolerance for that kind of outage.
    – Riley Major
    Commented Oct 7, 2014 at 14:37
  • @Kin - I found this answer which describes using Process Explorer. The trick will be catching the process during the brief few seconds it's up. There's another answer on that question which we are also researching.
    – Riley Major
    Commented Oct 7, 2014 at 14:49

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.