locked
Server: the operation has been cancelled. - when processing cube RRS feed

  • Question

  • When I highlight a few partitions and start processing, the process occasionally stops with a message that operation has been canceled, like this:

    Response 3
        Server: the operation has been cancelled.
    Response 4
        Server: the operation has been cancelled.
    Response 5
        Server: the operation has been cancelled.

    ..etc...

    (no further error message details are provided)
    SQL profiler shows that batch was completed (but rowcount shown in process progress log is too small).

    Analysis Services profiler shows no messages at that time at all. It just shows messages when it started, and when I restarted the processing.

    • The Analysis trace appears to be stopped when processing stops with "Server: the operation has been cancelled."
    • This is an occasional error and sometime occurs within 15-20 minutes from starting to process. It could fail on 1st partition in the process list , or on some partition in the middle. Some partitions might run for a few hours and not error out, but sometimes it fails quickly.
    • I was not sure if this is an issue with the underlying fact data, so I broke up the last partition where it failed into 5 smaller partitions, they processed OK separately.
    • I think restarting SQL Server and SSAS helps to process a few more partitions. Some that failed with this problem process OK after restart, but some fail again.
    (apologies for re-posting, wanted to put under more specific thread title)

    Wednesday, January 3, 2007 11:01 PM

Answers

  • Dec CTP has the same issue.

    I think there is more clues on why this is happening.

    I tried reducing the partition output to below 10M rows and it helped preventing this cancelation. So I changed back to 20M partition size (my last sizing) and ran again when watching the process memory utilization.

    it turns out when SSAS was getting closer to 9M rows its memory utilization climbed very quickly from 300-400MB to 1,400 MB and then to 1,800 MB and tried grab more. So at some point when there was not too much RAM available it cancelled out with an obscure error message.

    The above is on
    • Windows XP SP2
    • RAM 4GB (visible 3.7GB)
    • Dual Xeon Irwindale 3.4 GHz
    • SQL 2005 SP2 Dec CTP (Dev edition)
    this is very unusual for the few dimensions and dimension members that I have setup

    To compare, when I process the same cube on another box (see spec below) SSAS (msmdsrv.exe) process barely gets over 300MB memory utilization..
    • Windows 2003 Ent Sp1
    • RAM 4GB (all visible)
    • Dual AMD Opteron 246
    • SQL 2005 SP2 Dec CTP (Ent edition)
    So this system goes through 20M partitions without a snag.
    Also
    I changed the partition size from 20M to 100M and it still did not use more than 400MB(msmdsrv.exe).

    Question is why Windows XP version of SSAS grabs that much more memory than Windows 2003 Edition? I am suspecting there may be a bug with dimension caching that has been improved in SSAS 2005. (as suggested in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=264030&SiteID=1 )

    so msmdsrv.exe on XP looked like this:

    processed rows memory
    up to 6M 400MB
    9-13M 400MB-1,900 MB
    13M-20M back to 400MB

    there is some peak around 10M-13M rows where it tries to grab tons of memory. That does not happen on Win 2003 Server - it always coasts around 400MB.


    Monday, January 8, 2007 2:00 PM

All replies

  • What is happening here. And I making guess. Not having ability to see the situation leaves room for guessing.

    You are running into situation where serveral processing jobs are fighting to aquire a lock to finish processing ( to commit processing transaction). The jobs that cannot aquire lock are waiting for some period of time and then simply cancel themselfes.  Analysis Services trace should have been more explicit telling you who initiated cancelling of processing of every specific partition. I think I have seen this problem reported on Connect alreday, so it will get attention.

    Now what to do in your case: Instead of starting serveral processing jobs you should try to create a single "batch" command that is going to include serveral processing commands. In this case Analysis Server will have a single transaction for all partitions processed in the batch, and there will be no contention.

    About problem of trace getting cancelled. Fortunately that is another problem that has been reported alreday and has been fixed in upcoming SP2. But in your case if you change processing to happen inside a single batch, you should not run into this ever again.

    HTH.

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

    Thursday, January 4, 2007 6:34 AM
    • It happens when I run only one partition as well.
    • Analysis services trace does not show any entries when the self-cancel occurs. In fact the trace stops by itself (bug?) - i noticed you said it's being fixed for next SP2 release?
    • When running more than one partition processing I run them in "Sequential" mode with "Separate Transactions" setting - which removes the contention.

    Thursday, January 4, 2007 2:01 PM
  • Please try and process the single partition you saw faling using SQL Management's studio processing dialog.

    See if you get any processing errors in the processing dialog.

    There are multiple things could be responcible for the behavior you experiencing. Please contact customer support and let them look at your case. It is really hard to suggest anything for you in this case.

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

    Friday, January 5, 2007 3:16 AM
  • I tried running the processing of one partition through SQL Management Studio - get the same self-cancelation result.

    I created a clustered index on my main large fact table (has 630M rows) - replaced a non-clustered index and it gets data faster, but still cuts off at around 9M rows.

    I am thinking it actually pre-reads more rows and may be fails at some treshold like 25M or something but does not report the actual count correctly?


    Friday, January 5, 2007 3:58 PM
  • Try installing latest CTP of SP2 from http://www.microsoft.com/downloads/details.aspx?FamilyID=d2da6579-d49c-4b25-8f8a-79d14145500d&DisplayLang=en  and see if still get the error. But regardless, the situation you describe does look like a bug in the product.
    Reporting it will allow product team to fix it. The situation looks quite unique, this is first time I hear about something like this.

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

    Saturday, January 6, 2007 3:26 AM
  • Dec CTP has the same issue.

    I think there is more clues on why this is happening.

    I tried reducing the partition output to below 10M rows and it helped preventing this cancelation. So I changed back to 20M partition size (my last sizing) and ran again when watching the process memory utilization.

    it turns out when SSAS was getting closer to 9M rows its memory utilization climbed very quickly from 300-400MB to 1,400 MB and then to 1,800 MB and tried grab more. So at some point when there was not too much RAM available it cancelled out with an obscure error message.

    The above is on
    • Windows XP SP2
    • RAM 4GB (visible 3.7GB)
    • Dual Xeon Irwindale 3.4 GHz
    • SQL 2005 SP2 Dec CTP (Dev edition)
    this is very unusual for the few dimensions and dimension members that I have setup

    To compare, when I process the same cube on another box (see spec below) SSAS (msmdsrv.exe) process barely gets over 300MB memory utilization..
    • Windows 2003 Ent Sp1
    • RAM 4GB (all visible)
    • Dual AMD Opteron 246
    • SQL 2005 SP2 Dec CTP (Ent edition)
    So this system goes through 20M partitions without a snag.
    Also
    I changed the partition size from 20M to 100M and it still did not use more than 400MB(msmdsrv.exe).

    Question is why Windows XP version of SSAS grabs that much more memory than Windows 2003 Edition? I am suspecting there may be a bug with dimension caching that has been improved in SSAS 2005. (as suggested in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=264030&SiteID=1 )

    so msmdsrv.exe on XP looked like this:

    processed rows memory
    up to 6M 400MB
    9-13M 400MB-1,900 MB
    13M-20M back to 400MB

    there is some peak around 10M-13M rows where it tries to grab tons of memory. That does not happen on Win 2003 Server - it always coasts around 400MB.


    Monday, January 8, 2007 2:00 PM
  • It is great you've been able to figure it all out!
    I highly recommend anyone reading this post and having similar side effects monitor memory consumption during processing.

    Analysis Server does not have a separate version that compiled for XP vs. Win2003 server.The same executable is being installed in both cases.

    The differences you are seeing are not related to Analysis Server asking for more memory from XP. These are side effects of operating system being more advanced in case of Win2003 server. XP is a desktop operating system and is not intended support server applications performing heavy operations. The Server OS, as you see in this example, is way more efficient with things like: memory management, thread management - it is better tuned to provide services to highly demanding servers.

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

    Monday, January 8, 2007 9:09 PM
  • SSAS takes 1.8GB of RAM when running on Windows XP
    and only 400MB when running on Windows 2003 Server.

    how is this related to the operating system?

    are you saying under Windows XP SSAS needs to use 100 bytes to store a double and only 4 bytes on Windows Server 2003?

    obviously SSAS is asking the operating system to allocate that space, so it looks more like a memory usage bug that manifests itself on Windows XP.

    Monday, January 8, 2007 11:15 PM
  • I know it is hard to beleive and it might look like the difference is big, but in software sometimes the little things can make a huge difference. Analysis Server allocates memory in several steps and then returns it to OS in several steps. At any point of time many things can affect memory footprint.

    Again, you are running exactly the same executable on both platforms. The size of the datatypes not even dependant on the platform you running.

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

    Monday, January 8, 2007 11:57 PM
  • are you referring to the memory allocation where there are virtual gaps in the alocated space? If there is some lack of support of that on Windows XP, then it would need to be properly implemented in SSAS.

    Otherwise it it defeats the purpose to even have a development version if the behavior of SSAS on Windows XP is so unreasonable.

    I hear way too many things about how the development version is deficient in this way and another way, lets not forget that it all starts from development. If I can't prove that this product works in development environment then I wont be able to justify purchases of production hardware.

    So Analysis Services Team:

    please make a reasonable effort to make sure the product works ok in the development setup.

    Tuesday, January 9, 2007 1:13 AM
  • We took are receiving this "Server: The operation has been cancelled." error while processing dimensions.

    We have plenty of memory available for SSAS2005, 14GB and am running on WIN2K3 Server with SP2 CTP installed. We still see the issue with SP2. The most memory I've seen it consume is 10GB.

    It's coming to be quite annoying and problematic. We also run other cube processing jobs at the same time, however they are going against other databases. It's not possible to process all the cubes in a single job.

    Any ideas?

     

    Wednesday, January 17, 2007 9:17 PM
  • was this problem addressed in SP2 final?

    Thursday, February 22, 2007 2:28 AM
  •  

    Looks like this problem got worse after sp2. I am not able to build anything for the past 1 week. I just get server : the operation has been cancelled.
    Thursday, December 20, 2007 5:09 PM
  • Has this ever been solved?  I am running on a 64-bit processor and I am now getting this message.  For almost 3 months everything has been running well and this just started happening this week.  My guess is that this is a volume related issue and that the source data is just too big now.  Like everyone else, the problem is not every time and things like restarting Analysis Services appears to resolve the problem.

     

    Thanks in advance for any help.

     

    Dave

     ===

    Our problem stemmed from the fact that SS2005 was installed on a 64-bit system, but the administrator of the server loaded SS2005 32-bit version.  After reinstalling SS2005 under the 64-bit version, our problem was resolved.

    • Proposed as answer by DaveDoyle Thursday, April 30, 2009 12:27 PM
    • Edited by DaveDoyle Thursday, April 30, 2009 12:29 PM
    Tuesday, May 20, 2008 5:46 PM
  •  

    i have the same problem with 16GB memory.

    AS2005 + SP2 Cum Update 5

     

    it started after i designed aggregations.

     

    Writing Data ok...

    Building indexes ok..

    Building Aggregations ok...

     

    Everything goes perfect until (MergingAggregations) step.

    At this point at got the same error and everthing roll backs.

     

    Server: the operation has been cancelled.

     

    Tuesday, May 20, 2008 8:47 PM
  • This happens to me when processing a cube on a windows 2003 server VM hosted on an XP workstation...
    Tuesday, May 19, 2009 6:12 PM
  • Cem, are you running on XP - I suspect not if you have 16GB of memory.  Does anyone have any insight into how to fix this problem? Edward, has MS figured out what is going on here?
    • Proposed as answer by Adrian Castro Tuesday, February 9, 2010 4:53 PM
    Tuesday, May 19, 2009 6:16 PM
  • Tuesday, February 9, 2010 4:54 PM