none
Could not continue scan with NOLOCK due to data movement.

    Question

  • We recently upgraded to SQL server 2008 R2 from SQL Server 2005.

    I saw this error from one of the stored procedure...really have no idea why this is happening...all of sudden...

     

    any help will be great.

     

    Thanks.

    Monday, December 12, 2011 7:22 PM

All replies

  • Hi SA1579,

    Have you  set transaction isolation level to READ UNCOMMITTED in your stored procedure? If yes, as READ UNCOMMITTED is equal to NOLOCK,this error occurs because you are reading with the NOLOCK hint .
    If someone comes along and does an insert or update that causes a page split and the data your dirty reader was reading gets moved to another page, you'll get this error.Best is to not use the NOLOCK hint and fix the underlying issue that was causing the deadlock in the first place.

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, December 13, 2011 5:31 AM
  • what is error..?

    or second thing you do not want to drill down use generate script wizard and fetch that SP script and change the option in advance option script for sql server R2 or which server you are and recreate that sp at your end.

    Tuesday, December 13, 2011 5:57 AM
  • Thanks for the reply.

    No I have not used READ UNCOMMITTED or NOLOCK in the stored procedure.

     

    The stored procedure calls another stored procedure to insert data into a table variable...something like:

    Insert into @table1( val1, val2, val3, val4)

    exec spAnother parm1, parm2

     

    This call throws the error.

    Tuesday, December 13, 2011 1:22 PM
  • output a print @sql, then cut/paste the @sql value into a new query window, check if it is working or not.

    else might seems database was currupt from which you restore.

    Wednesday, December 14, 2011 4:28 AM
  • Which patch version are you using?  I was running SP1 10.50.2500 and was getting the error.  Once I upgraded to CU4 (10.50.2796) the issue went away.   http://support.microsoft.com/kb/2633146
    • Edited by ChiliPepr Thursday, January 12, 2012 9:51 PM added link
    • Proposed as answer by ChiliPepr Thursday, January 12, 2012 9:51 PM
    • Unproposed as answer by ChiliPepr Wednesday, January 18, 2012 8:29 PM
    Thursday, January 12, 2012 9:50 PM
  • http://support.microsoft.com/kb/960770/nl

    This posting is provided “AS IS” with no warranties, and confers no rights.

    If this reply answers your question, please mark it as Answered for others to find it easily.
    If this reply help you resolving the problem, please vote the post as Helpful.

    Thursday, January 10, 2013 10:44 AM