Processing hangs

Hoping someone out there knows this error or possibly can help me to find out the reason.

We have a SQL Server 2008 relational Database as the source for SQL Server 2008 cube. The cube will be processed by a SQL Server job using XMLA command.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Parallel>
  <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
   <Object>
    <DatabaseID>MyDbName</DatabaseID>
   </Object>
   <Type>ProcessFull</Type>
   <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
  </Process>
 </Parallel>
</Batch>



Our environment is:
Windows Server 2008 SP2, x64
MS SQL Server 2008 Enterprise SP1
VM-Ware Maschine
Processor: AMD Opteron 8381 HE (QuadCode 2,5GHz)
Memory: 8 GB


The problem is: sometimes (not always) the job step for processing hangs until we restart the analysis service.
The CPU is not busy, no error entries in windows event log, no entries in msmdsrv.log (Analysis Services-Log), no activity on analysis services traced by profiler…
And to be honest, I have no further idea where can I trace the error cause.

What we have already tried:

  • Change xmla script to sequential processing
  • Limit the analysis services memory usage to LowMemoryLimit = 30 and TotalMemoryLimit = 60
  • Limit the sql server memory usage to max. 70%
  • Set CommitTimeout to 10 min (in a hope the processing will be aborted after 10 min hanging)

My feeling is that the processing will be performed and while commit the changes (before the new cube files will be used) it hangs. In the cube file folder, all files are refreshed. If I try to access the cube using Excel, the cube is not yet refreshed.

So, may be someone knows this issue or could help me to find the source.
Thank you in advance for your help
Anatoli.

September 8th, 2010 9:02am

I had a similar issue on a similar setup, and even though I couldnt find any reason behind it, I found out that if you actually keep pinging the Analysis server, this will not happen. So what I did is that I created a simple report with some simple MDX queries, and kept it running in the background via a subscription once in every 9 minutes. And strangely, it worked! Next time the cube processing hangs, just try running a simple MDX query in SSMS to the same server, and watch the activity in SQL Server profiler. For me, the process "woke up" after almost 40 seconds after I ran the query.

I think the latest hotfixes/patches might have solved this problem.

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2010 9:54am

Hi Jason.

Thank you for this hint. After reviewing the behavior adding your information, the results of our tests make sense for the first time. I'll try your workaround in our environment and writte again, if I have new informations. Thanks again.

Is it a bug? May be documentated by MS? Does someone know that?

Greetings,
Anatoli.

September 8th, 2010 1:58pm

Could you verify whether you are able to "wake up" the server by running some queries? If yes, both of us would have the same issue and I could actually raise the issue with Microsoft. Before, I couldnt find anyone who has encountered the issue so I was guessing there could be something wrong in my setup. So please confirm
Free Windows Admin Tool Kit Click here and download it now
September 8th, 2010 2:02pm

Hi Anatoli,

Have you looked at a SSAS Profiler trace captured when the processing hang occurred?

Also, from your original xmla script, you are doing a ProcessFull on the entire database. What happens if you break the xmla script into two separate xmla scripts: execute 1 xmla script to process all the dimensions; then, after the processing of all dimensions is completed, execute the 2nd xmla script to only process the cube.

If you are not already running the latest cumulative update for SSAS, you night want to try that and see if it helps.

It may be helpful for you to have a support case created so we can assist looking at Profiler traces and perhaps capture/analyze a couple of hang dumps when the processing hang happens.

http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

Thanks,

Cathy Miller

September 14th, 2010 6:25pm

Hi Cathy,

We will finalize our tests this week and after that I will write the feedback here.

 

Regarding the updates - We cannot install the updates, because we aren't admins on these servers and we use them only as a service.

 

Greetings,

Anatoli

Free Windows Admin Tool Kit Click here and download it now
September 15th, 2010 8:11am

Crazy - this is realy the solution for this problem. Thank you, Jason, for this very special workaround.


Greetings,
Anatoli.

September 21st, 2010 8:28am

Just FYI in case it helps someone else...

We also had the same issue when performing a full cube process on the database - we were processing all the dimensions first and then performing a ProcessFull on the cube.

To resolve the issue we have to split the process full into ProcessData and ProcessIndex - this resolved the issue.

  • Proposed as answer by Avelino Rego Monday, October 11, 2010 1:43 PM
Free Windows Admin Tool Kit Click here and download it now
September 21st, 2010 9:53am

I can imagine Katherine's solution also working, because in my case, it was the Index processing which used to hang. Need to try out this and check
September 21st, 2010 10:20am

Hello.

We have the same problem here.

Our environment is:

Windows Server 2008 R2 Enterprise, x64

MS SQL Server 2008 Enterprise SP1

processor: Intel ® Xeon ® E5345 processor memory: 28 GB.

Both solutions have acted

* 1 process the dimensions and then process the cube in two phases-data and indexes.
* 2 "wake up" the server with queries.

For now, the solution * 1 was adopted.

About the latest Service Pack, SP2, we didn't find any reference to this problem, in the release notes.

Without recourse to workarounds, already someone's gotta fix this behavior?

 

Greetings,

Avelino

Free Windows Admin Tool Kit Click here and download it now
October 11th, 2010 2:10pm

Hi all,

here we have the same problem. Any news or # of support case open to Microsoft?

 

Regards,

Massimo

October 7th, 2011 12:19pm

It is a few years later.  But I thought I'd mention that it happens for me too on SQL 2008 R2 SP2 CU5.

Its extremely rare for us, however.  It seems to have started occurring when we moved from an old physical SQL Server on SAN to a new VM using NAS.  Maybe that is a coincidence.

It was kind of a shock to behold.  I used XMLA commands to disconnect every other SSAS session and connection, and I used SQL profiler to ensure they all died.  The only thing left "running" was the XMLA <Batch> that was supposed to be processing the database.  It was doing full processing on the entire database and it was hung even though it was the *only* active session connected to the entire instance of SSAS.  Windows performance counters showed nothing for CPU or disk.

Fortunately there was another database on the SSAS instance and as soon as I started an MDX query on it, SSAS woke up and finished its work.

Maybe its time to try SSAS SQL 2012.  But Microsoft probably hasn't fixed multidimensional bugs in the past 5 years anyway.  Its all about tabular now, so I hear.

Free Windows Admin Tool Kit Click here and download it now
April 4th, 2015 9:46pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics