Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Could not continue scan with NOLOCK due to data movement. Expand / Collapse
Author
Message
Posted Monday, October 19, 2009 3:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 31, 2011 8:56 AM
Points: 178, Visits: 185
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!
Post #805290
Posted Monday, October 19, 2009 7:32 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Micheal Earl has not posted in many months.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #805366
Posted Sunday, March 16, 2014 11:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 6:12 PM
Points: 2, Visits: 111
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 dbcc checkdb ('DB NAME') 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!
Post #1551642
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse