Wednesday, March 21, 2012

MSSQLServerOLAPService crashing frequently

System: Sql-server 2005 (x64) SP1 , Windows 2003 Server (x64)

SSAS seems to crash now and then and application log fills with :

The description for Event ID ( 22 ) in Source ( MSSQLServerOLAPService ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured..

and...

The description for Event ID ( 22 ) in Source ( MSSQLServerOLAPService ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured. Internal error: An unexpected exception occured..

etc.

System log at just before: (event id 7034)

The SQL Server Analysis Services (MSSQLSERVER) service terminated unexpectedly. It has done this 2 time(s).

Please contact Customer Support to report your problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The following messages have been happening on our system (Windows 2000 SP 4) while it was at SQL Server SP3 and AS SP2.

-Unexpected fatal error occurred. Attempting to restart server.

-mssqlserverolapservice service terminated unexpectedly. it has done this X times

We upgraded to SP4 on both SQL Server 2000 and AS. Same issue is still happening after the upgrade which was one of the recommendations.

What are we missing at this point?

|||

Let me make a wild guess here: You might have case of data corruption.

If you have kept your backups and you know how to restore your Analysis Services cubes here is what you can do:
Cleanup your data folder. For AS2000 that should be something like "C:\Program Files\Microsoft Analysis Services\Data". Delete everything from that folder.
You will loose all of you Analysis Services data, and you should only do that if you sure you can recover later.

If you are not sure you can get all of your data from backups, you should contact customer support and CSS guys will help you to recover from data corruption without loosing data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Bizarre - we're getting this same behavior.

It's crashed every morning for the last week at about the same time....3-4AM. We're also on a x64 box, wonder if that has anything to do with it.

Ever figure out what the deal was?

|||

The solution I implemented was to upgrade to the latest SP on AS (I believe it we upgraded to SQL 2000 SP4, and AS SP 4 as well) - (AS Version 8.0.2039). Once we had the latest SPs in place, we proceeded to backup the entire <MSSQL\Microsoft Analysis Services\Data folder containing all the AS databases onto a different location as a fall back position. At this point, (with an empty /data folder) we proceeded to start the AS related services (MSSQLServerOLAPService) and they were steady. I then proceeded to iteratively re-introduce the AS Databases one folder at a time from the previously backed up location (<backuplocation>\MSSQL\Microsoft Analysis Services\Data\<AS databases> until the folder that caused the services to start crashing was identified. Once this folder was identified, I deleted it from the /data folder and process the appropriate dimensions/cubes to regenerate the AS databases. Make sure that your .odb file is backed up safely as an added precaution.

|||

Hi,

I have this exact same problem error but it happens only when the cubes are being processed. In the middle of the dimension and cube processing AS crashes, returns that unexpected fatal error message to the event log, and then restarts. I've scheduled a Job that process the cubes after we load new data to our data mart, so SQL Server Agent is process that runs the job on the cubes. This happens erratically some days, some not.

If the process fails I go to Analysis Manager and reprocess the cubes without problem. I've seen this even happen in Analysis Manager, A first attempt to process fails and crashes the server, and then a second one works. Pretty strange. I have SP4 for both SQL and AS.

If anyone know how to deal with this I'd appreciate your advise.

Thanks,

Ricardo Sada

|||

we eventually opened up a case with MS, and found a resolution.

i wrote about it here: http://jesseorosz.spaces.live.com/blog/cns!E322FD91218E57CF!295.entry

basically it's a problem with the OLAP heap, we switched to use windows low frag and it's worked OK since

|||

Thanks Jesee

I read your post and I see that you are using SQL 2005, in our case we are still working with AS 2000.

May be the cause is similar and some out of Memory problem is the issue.

1 comment:

latesttechnologyblogs said...

Power Bi is a business analytical solution that let you visualize data and share insights across your organization and connects to hundreds of data sources and brings your data to life with live dashboards and reports. Learn at more Power bi online

Post a Comment