Cube Processing Timeout Errors

I’m on holiday now until the beginning of January, getting some well earned rest. That is until I remember I have a 4 month old daughter who is currently unwell and wishes to vocalise her displeasure of this rather than getting some sleep. Screaming at the top of her lungs is probably not going to make her feel any better, especially if she has a sore throat already! I now appreciate why parents find it so hard not to give advice to their children even when the kids have grown up because for the first years of their life you actually DO know what’s best for them, and it’s probably a hard behavior to get out of.

Before finished work last week, I had to get one of our staging environments ready for some UAT, which involved refreshing the data warehouse and updating the cubes. Some of the cubes I have to manage are big… one cube in particular is really big…. over 2TB at last count. And managing cubes of such size can be challenging: After I had restored our data warehouse to the staging environment, rather than trash the data that was already loaded into the cube, I added the partitions to the cube that was already in the staging environment. This left the cubes state as partiallyProcessed (the historic data was already loaded, it was just the last months worth of runs that were unprocessed). I anticipated that processing these partitions would take overnight at least. So before I left work that evening I set the cube to run a process default that also ran a process update on the dimensions with processing affected objects (didn’t want to lose all those aggregations!)

Next morning however I was greeted with a window of red X’s from SSAS and the following error message:

Internal error: The operation terminated unsuccessfully. OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.

ssasTimeout1I’d never seen this before! So I consulted the online documentation for that error message. Turns out there’s a connection timeout property in the advanced settings for SSAS. Just right click on the instance in SSMS and go to Properties.

ssasPropertiesBy default the ExternalCommandTimeout value is 3600, which is 1 hour.

externalCommandTimeout

I increased it to 14400 (4 hours). If it timed out after that then I had bigger problems… in our production environments all new runs are added via ProcessAdd and do not take long. I also know that the query running in this environment will take a long time because of the size of the tables being queried. It’s probably worth questioning if increasing the time-out really is the fix, or that you need to do some more investigation first. If you don’t see the ExternalCommandTimeout then click on “show advanced (all) properties”.

Took another evening of processing to run, but it ran successfully this time. So all was done before I took my holiday. Having had only 2 weeks of paternity leave in August, and before that my time off was in February, I’m taking a break from posting until the new year, unless I get some sort of epiphany that I simply must share. Given the likelihood of that, I think I’ll wish you all a happy holiday season right now! 🙂

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s