Hello,

I have SSAS 2005 64bit EE.
Our DW has 2 cubes and about 40 measure groups.
We have SSIS package that builds XMLA command to process SSAS database.
We have a problem, that quite often when we submit process job, analysis
services might get "stuck". That means no CPU is used, but job never comes
back. Also, cubes are not processed in such case.
Restarting SSAS and resubmitting job almost allways helps, but not 100%.
Manually reprocessing SQL Server Management Studio usually works also.

So, I am trying to find if anyone else experienced similar problems.

Thanks

Hello,

I understand that you sometimes encoutner AS "stuck" issue when you submit
process job of SSIS package. If I'm off-base, please let's know.

To find out the root cause of this issue we may need to analyze memory
dumps when the issue occurs, this work has to be done by contacting
Microsoft Product Support Services. Therefore, we probably will not be able
to resolve the issue through the newsgroups. I recommend that you open a
Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

Please see the following article on how to generate memory dump when the
issue appears. Also, you may want to enable profiler trace so that you
could find which operations are stuck.


919711 How to configure SQL Server 2005 Analysis Services to generate
memory dump files
http://support.microsoft.com/default...b;EN-US;919711


Also, I'd like to confirm if you have installed SQL 2005 sp1 for AS since
some known issues are resolved in SP1. Also, there was a bug related to
having more than one partition on a distinct count measure (you do have
some of these).

917870 FIX: An MDX query returns no results and you cannot cancel the
operation in SQL Server 2005 Analysis Services
<http://support.microsoft.com/default.aspx?scid=kb;EN-US;917870>

You may want to apply the post SP1 hotfix rollup: 918222 Cumulative hotfix
package (build 2153)
for SQL Server 2005 is available
<http://support.microsoft.com/default.aspx?scid=kb;EN-US;918222>


You may also want to check if there is memory pressure on the system when
the issue occurs. SSAS will process the cubes if it has the system
resources to do so. This of course will depend on the size of the cube and
amount of data. Since SSAS reads all the data from the source database and
then stores the information in memory to them populate the dimensions and
cubes. Therefore the bigger the cube, the more data in the source database,
the more resources necessary. The process, depending on the data, will be
time consuming and will stop should the resources not be sufficient to
proceed (that is why the cube processing would fail in your SSAS). With the
large amount of data the project, I would highly recommend upgrading to
Enterprise Edition if not in order to increase memory. With 2 GB, even if
SSAS process would not hang, the cube processing would take ages to
finalize.


In order to help reduce memory need at processing time, you may choose
certain options, such as the method of processing (sequential or parallel).
You may also want to set max memory of SQL Server engine so that it may
not take too much memory.

If you have any concerns or questions on above information, please feel
free to let's know. Thank you.


Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.