Could not continue scan with NOLOCK due to data movement.

Question

Post reply

Could not continue scan with NOLOCK due to data movement.

Adam Bean

One Orange Chip

Points: 26631
More actions
March 24, 2008 at 11:54 am

#121171

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?

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Michael Earl-395764

SSC Guru

Points: 53873

March 24, 2008 at 12:29 pm

#793149

Is your SQL Server running on VMWare?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Adam Bean

One Orange Chip

Points: 26631

March 24, 2008 at 12:31 pm

#793151

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 3

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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]

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

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.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

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.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

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]

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

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

Scott Schommer Old Hand Points: 367 More actions · Answer 9

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!

DoubleD SSC-Addicted Points: 430 More actions · Answer 10

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!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

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]

Tim Roberts-250428 SSC Veteran Points: 244 More actions · Answer 12

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! 😀