Could not continue scan with NOLOCK due to data movement.

  • This is crazy ... I'm getting this error on a database that has zero activity to it, when attempting to do an updatestats with fullscan. Thus far everything I've read about relating to this error is due to using a NOLOCK hint, which I am not ...

    Do I simply need add a read uncommitted into my procedure?

    Any thoughts?

  • Is your SQL Server running on VMWare?

  • No, I'm testing locally against my own machine ...

  • just stick to only rebuilding indexes as part of maintenance and no need to run update statistics

  • Could you enter the error text in a post? The title is getting cut-off halfway through for me.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • rbarryyoung (3/24/2008)


    Could you enter the error text in a post? The title is getting cut-off halfway through for me.

    Msg 601, Level 12, State 3, Line 1

    Could not continue scan with NOLOCK due to data movement.

  • SQL Noob (3/24/2008)


    just stick to only rebuilding indexes as part of maintenance and no need to run update statistics

    And just forget about any non-index statistics? No thanks.

  • check for corruption?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • ah ha, that was it ... good call.

    Msg 8928, Level 16, State 1, Line 1

    Object ID 1419152101, index ID 5, partition ID 72057594157858816, alloc unit ID 72057594172669952 (type In-row data): Page (1:17819) could not be processed. See other errors for details.

    Msg 8939, Level 16, State 98, Line 1

    Table error: Object ID 1419152101, index ID 5, partition ID 72057594157858816, alloc unit ID 72057594172669952 (type In-row data), page (1:17819). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.

    Msg 8928, Level 16, State 1, Line 1

    Object ID 1419152101, index ID 5, partition ID 72057594157858816, alloc unit ID 72057594172669952 (type In-row data): Page (1:17851) could not be processed. See other errors for details.

    Msg 8939, Level 16, State 98, Line 1

    Table error: Object ID 1419152101, index ID 5, partition ID 72057594157858816, alloc unit ID 72057594172669952 (type In-row data), page (1:17851). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.

    Rebuilt my indexes and all is well.

    Thanks

  • Why do you ask if the server is VMWare? I am asking because we experienced the same error on a SQL 2005 box on an ESX server. Please let me know and thanks!

  • I get the same error on a VMware machine. Can you tell me what this means? There must be a reason why VM was questioned.

    Thanks!

  • Micheal Earl has not posted in many months.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • This post really helped me and together with the previous post from RBarry Young I was able to resolve the problem.

    I was experiencing the NOLOCK error when running a select statement on a read only log shipping secondary database. The same database on the primary ran the statement with no issue. It turns out a large index had errors which I discovered when running [font="Courier New"]dbcc checkdb ('DB NAME')[/font] on the secondary read only database. I ran the same statement on the production database and it had no errors. I rebuild the index on the primary production server using a maintenance plan, then manually shipped over the logs and applied them. After which the database on the secondary log shipping server started running the query properly.

    I now understand NOLOCK is applied by default when running a select statement on a read only database, so removing it from the statement makes no difference.

    Many thanks to all those that contributed to this post and got me out of a pickle with a very demanding customer! 😀

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply