Here is something interesting that came up a newsgroup thread a little while ago, that I though merited some more explanation.

Have you ever seen "the operation has been cancelled" errors from a long running query (one that takes more than 30 seconds) while a cube or partition is being processed?

What you may be seeing is a  "by design" behaviour, where a pending commit lock will cancel long running queries that are blocking it from completing. There is a property that can be set at both the server and processing command that controls this behaviour called ForceCommitTimeout which defaults to a value of 30 seconds.

Books Online has the following description of ForceCommitTimeout


A signed 32-bit integer property that defines the timeout, in milliseconds, before a commit should cancel other commands that preceded the current command, including queries in process.

The default value for this property is zero (0), which indicates that other commands will not be forced to timeout when the current command times out.

As you can see from the screen shot below, this is not entirely accurate. The default value is actually 30 seconds (30000 milliseconds). I'm not sure if this changed with SP2, but this might explain the couple of recent threads on the newsgroup.

In order to understand more about this setting we need to answer the question of what exactly is going on during processing?

  • When an object is processed a new version of the object is created and this new versions is where the processing actually takes place.

Take for instance dimension Reseller. First time you process this dimension you will see in the dimension folder files with names like 1.(All).astore … 1 stands here for the version of the dimension. Once you process the dimension you will see files with version 2 the file above will change to 2.(All).astore. So during commit operation the old set of object files dies and new files then start being used by Analysis Server.

The DDL definition of the Reseller dimension which is kept in the Dim Reseller.1.dim.xml file will go away and you see new file created Dim Reseller.2.dim.xml. If you look inside this file, you will see <ObjectVersion>2</ObjectVersion>

  • When the processing is complete a "pending commit" lock is placed on the object that was being processed (and any dependant objects). If there are any current queries executing against the object in question they will be allowed to continue, any new queries will be queued up until the current version can be swapped out for the new version.
  • If the ForceCommitTimeout has expired and there are queries still executing they are cancelled and the old version is swapped out for the new one and any queries that were queued up are allowed to commence.

This means that increasing the ForceCommitTimeout will give currently executing queries more time to complete. But it also means that queries that were executed soon after the "pending commit" lock was taken will be stalled for the timeout period before they even start to be executed. This will result in the perception of inconsistent performance as any queries executed during this window might take nearly twice as long to produce results.

The "pair" to this setting is the CommitTimeout, where you could cause the commit of the process operation to timeout and roll back, allowing currently executing queries to continue to completion. I can't really see people using this option nearly as much as the ForceCommitTimeout as you are basically saying that Queries are more important than the Processing operation.

(If you are interested the full thread that sparked the idea for this post can be found here:

 Thanks to Edward for his assistance with some of the details of this post


# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"


# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Right-clicking on the server in SSMS and going into the properties and changing the value should work. If this does not work for you and it is urgent you should ring Product Support. 11/25/2008 7:24 AM | Darren Gosbell

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

How can I set the CommitTimeout and/or ForceCommitTimeout when processing a cube?

I want to process some Dimensions and Cubes but user queries are blocking the commit phase of the Processing and locking the entire database. So our preferred method would be to 'kill' the user queries blocking the commit and allowing the commit to succeed.

Any help appreciated, thanks
JB 9/11/2009 8:06 PM | Jonathan

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

@JB - I believe that these are server settings and that you have to set them at the server level as I outlined in this post 9/13/2009 10:11 PM | Darren Gosbell

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Simple fix but can cause a massive mulitple cascading freakout if not upped to a over 60 seconds
10/10/2009 1:47 AM | Saved my B*tt -- thanks!

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Yes, you may want to tune this relative to the average response time of your queries. Just keep in mind that any query requests that start after the commit lock has been requested will be stalled until the commit takes place, therefore they could do nothing for 60 seconds until the commit is processed before they can start running. Effectively making the user wait 120 seconds for their query to return. 10/10/2009 10:59 AM | Darren Gosbell

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

"property that can be set at both the server and processing command "

Suggests that there is an option both at the server level and per processing command.

Any Ideas?

Attila 10/16/2009 12:32 AM | Attila

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

@Atttila - I'm not sure I understand the question. Ideas about what?

You can definitely set this option at the server level from the server properties in Management Studio.

And this article lists both properties as properties that can be set in the Properties element of an Xmla command. 10/16/2009 6:51 AM | Darren Gosbell

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Hi Darren,

My B*tt is not entirely saved at this point. The ForceCommit has helped to a degree but not entirely. The Freakouts continue. They love the cube but it is like an abusive spouse or something... the AS server gets a little irritable (memory) and starts the Excel 2007 Pop ups randomly...

I've noticed that we are facing the same issues in this thread but instead of a Parent-Child Dimension, we have muliple Calcs 30 that seems to trigger the Server Operation Cancelled when these are dropped in a Cross Join. :

I guess I'm looking for a list of issues that can trigger this: (e.g.)
1. Hardware
2. Software - Win Server Settings (3GB switch on 32 bit) - Limits on Standard vs. Enterprise.
3. AS Server Settings
4. Dimension Structure
5. Max Cube Size
6. Disk Speed
you name it -
7. missed patches
8. Profiler Traces running
9. Memory Insufficient
10. Triggers for MDX Query Execution going to Cell from Set vice versa
11. Queries will Cube-Dims Processing even on another Cube.
12. AWE Enabled?
13. Big Dimensions -- what memory does it grab? During crossjoins? etc...
14. Big Calcs across 7-8 multi-million (80) row Fact Tables?
15. Is there a rough dead stop limit for Fact Tables for 32 Bit-- (like looking at the Folders and Dim files and the server -- at count of X Dims of XXXX size)

Clearly on a Win 2003-Standard AS on 32 bit with 4 GBs of Ram, there will be issues with Scalability. The problem is after restarting the OLAP Service... the baby is beautiful for a few hours until later in the day when the Caching reaches a limit or the OLAP Service can't create Sessions and then the AS pops the Excel Red Xs causing Freakouts. "Server Operation Cancelled."

Any guidance is greatly appreciated. This can lull a developer... development works ffine... but a user with 20 tabs in Excel hhits Connection refresh and the trouble starts.

Is 32 Bit on AS only for testing and development? It seems to me it is impossible to do any Prod delivery with 32 bit even with AWE. Correct? 10/17/2009 12:40 AM | Saved my B*tt -- thanks!

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

one more:
16. Settings in the msmdsrv.ini which can affect the Memory management for this Error... 10/17/2009 1:31 AM | Saved my B*tt -- thanks!

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Hi Daren,

In xmla proceesing command, I set MaxParallel to 1 since I had Memory allocation failure: Not enough Storage. So I got: Operation has been cancelled error, and I set ForceCommitTimeOut to 0 as no one is quering my cube yet, however the error remained. Appreciating your advice.

Amir. 6/16/2010 12:02 AM | Amir K.

Post a comment




About Me
I am a consultant, based in Melbourne Australia. I primarily work in the Business Intelligence area with SQL Server, although I also dabble in Content Management Server and .Net coding

Contact Me


Any and all code, software, examples, suggestions and anything else on this web site is available for you to use at your own risk. No warranty is expressed or implied.
Views and Opinions
The views and opinions expressed on this web site are not necessarily the views or opinions of my employer.

Subscribe in Bloglines Subscribe in NewsGator Online

Locations of visitors to this page


Tag Cloud

Article Categories


Post Categories

.Net Blogs

Aussie Bloggers

BI Blogs

CMS Blogs