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

    Pregunta

  • am getting error error "Could not continue scan with NOLOCK due to data movement." in SQL server 2008(RTM).where as its working fine sql server 2005..! Query to re-produce this issue.!

    drop table #TableX
    drop table #TableY 
    go
    Create Table #TableX (Date_ smalldatetime)    
    Create Table #TableY (YearsBack smallint)    
    
    insert into #TableX values( 1000)
    insert into #TableY values(1)
    go
    insert into  #TableX
    select  * from #TableX
    go 20
    
    insert into  #TableY 
    select  * from  #TableY 
    Go 10
    
     
    Insert Into #TableX (Date_)    
     Select DateAdd(yyyy,-1 * YearsBack,date_)    
     From #TableX    
     Cross Join #TableY    
     
    


     


    Siva

    martes, 18 de octubre de 2011 10:28

Todas las respuestas

  • SQL Server 2008 is on Service Pack 3 now - have you tired it with a patched version?

     


    http://lqqsql.wordpress.com
    martes, 18 de octubre de 2011 14:16
  • yep, thanks.. installed sp2. its working fine..
    Siva
    miércoles, 19 de octubre de 2011 6:08
  • but this issue happens in SQL server R2 SP1 also. but not consistently.! can anyone help on this issue..?
    Siva
    miércoles, 19 de octubre de 2011 8:15
  • Try to run a DBCC checkdb, a guy on this thread was having the same trouble and found it was a corrupt index.

    http://www.sqlservercentral.com/Forums/Topic473623-146-1.aspx

    Thanks,

    Stephen


    Please click "Mark as Answer" if you found my post helpful. Thanks, Stephen.
    miércoles, 19 de octubre de 2011 8:53
  • but this issue happens in SQL server R2 SP1 also. but not consistently.! can anyone help on this issue..?
    Siva

    1. Sample shows only # tables being used. Is that correct code?
    2. Check ERRORLOG if you see any error at the same time? Does it fail always on SQL server R2 SP1 also?
    3. I am unable to repro the issue so it has to be enviornment specific.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    miércoles, 19 de octubre de 2011 13:58
    Moderador
  • Yes Balmukund  its correct code am using #tables and getting this issue in SQL server R2 SP1 also . but am not getting this issue often.!
    Siva
    miércoles, 19 de octubre de 2011 14:15
  • Yes Balmukund  its correct code am using #tables and getting this issue in SQL server R2 SP1 also . but am not getting this issue often.!
    Siva

    1. How long does it take to finish?
    2. Anything in ERRORLOG?
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    miércoles, 19 de octubre de 2011 14:23
    Moderador
  • Yes Balmukund  its correct code am using #tables and getting this issue in SQL server R2 SP1 also . but am not getting this issue often.!
    Siva

    Hi,

    Did the checkdb come back clean?

    Thanks,

    Stephen


    Please click "Mark as Answer" if you found my post helpful. Thanks, Stephen.
    miércoles, 19 de octubre de 2011 14:27
  • its taking less than 2 mins to complete. didn't any suspections in error log.

     

    Stephen,

    am using only #temp tables. so hope, no index corruptions..!


    Siva
    miércoles, 19 de octubre de 2011 14:33
  • Is Read uncommitted active for the query?
    Please click "Mark as Answer" if you found my post helpful. Thanks, Stephen.
    miércoles, 19 de octubre de 2011 14:50
  • Yeah, but i tried read read committed too.. eventhough am getting the error.
    Siva
    miércoles, 19 de octubre de 2011 14:52
  • Ok. With Read Uncommitted enabled this might be a legitimate error. If the query causes page splits, then technically the data has moved and READ UNCOMMITTED and NOLOCK are essentially the same thing which could account for the error.

    The only bug related information I can find on it is this KB, but it looks like you're already passed that patch level.

    http://support.microsoft.com/kb/960770/nl

    I'll keep looking!

    Stephen


    Please click "Mark as Answer" if you found my post helpful. Thanks, Stephen.
    miércoles, 19 de octubre de 2011 14:56
  • One thing you might try is enabling trace flag -T1118. This allocates TempDB pages in extents, which may reduce some of the contention/page splitting? It may help you out?

    http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx

    Thanks,

    Stephen


    Please click "Mark as Answer" if you found my post helpful. Thanks, Stephen.
    miércoles, 19 de octubre de 2011 14:57