Analysis Services stops working

We are sporadically seeing analysis services cubes stop responding for no apparant reason. This has happened 3 times in the last 5 days. There are no errors in the events logs, and no dump files to be found anywhere on the server.

Once this happens, If I go into SQL Server Management Studio and try to browse any of the cubes, I get the following error message:

"Unexpected Error: Server: The operation has been cancelled". This message appears under the "Dimension" column heading in the browser pane.

In the detail fields portion of the browser pane is the message:

"The query could not be processed".

We are running Enterprise edition, 64-bit platform, Service Pack 2. It works great most of the time...just don't have any idea how to troubleshoot this further.

Anyone have any suggestions?

This sounds a lot like the automated query cancellation behavior added in SP2 for the case where allocations are failing and the server is unable to shrink memory. Still, we should only hit that case once unless something else has gone wrong in the server. What does the msmdsrv process's memory usage look like when it's failing? I'm interested in process private bytes, and also the MSAS cleaner memory shrinkable and nonshrinkable numbers.

- Brad

|||Thanks for respondibng. I will take a look at the memory usage next time it fails. Most likely in the next day or two.|||

Here's what I found under MSAS memory counters in Performance Monitor. (FYI, total memory on the server is 8GB)

Total Memory:

Avg: 8602630

Max: 8605659

NonShrinkable:

Avg: 8601991

Max: 8604350

Shrinkable:

Avg: 1347

Max: 1445

|||

OK, so that means that the service is in fact out of memory, and for some reason, it's unable to reclaim the memory. This explains the behavior you're seeing, but it's not at all clear why killing sessions isn't making some of that nonshrinkable memory into shrinkable memory.

It might be useful to look at the output of DISCOVER_MEMORYUSAGE to try to characterize the memory in use, but it's likely that I would need more information to diagnose the issue more accurately. It's probably time to open a support ticket on this one, as I would most likely need to see a memory dump to determine where and why the memory is tied up.

- Brad