question

karthik_ca2003 avatar image
karthik_ca2003 asked

could not continue scan with nolock due to data movement

could not continue scan with nolock due to data movement Error when executing the update query. None of the data movement is in progress.
sql-server-2012
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
If you could provide some information about what you were doing, the environment in which you were doing it, the DBMS... ...basically, give us some context with which to work!
1 Like 1 ·
karthik_ca2003 avatar image karthik_ca2003 commented ·
Hi Guys, thanks so much for your suggesstion, finally we have restarted the sql services , that locked process got released as of now. The same problm came for 2008R2 sp1 version, once we executed sp2 patch, it was clear, but it cmae now in 2012 sp1
0 Likes 0 ·

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Without context, it's difficult to say what the problem is. However, there's a good question / answer over on http://dba.stackexchange.com that covers the basics. http://dba.stackexchange.com/questions/8986/could-not-continue-scan-with-nolock-due-to-data-movement To hit the highpoints here: * Large data set * `NOLOCK` query hint Some solutions: * change your code so it only retrieves the right data rather than do a full scan; * alternatively, change your code to do these big scans when data isn't being updated. * Another option, if the scans are just for (say) building reporting sets, is to offload that data onto another database, keep it up-to-date using Log Shipping or Replication, and read that instead.
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
You can't do a NOLOCK and an UPDATE. Modifying data absolutely requires locks.
1 Like 1 ·
karthik_ca2003 avatar image karthik_ca2003 commented ·
Hi Thomas, Thanks.! But i m using Sql server 2012, When i execute on Stored procedure where i have set of DML statements, in that "update query" alone got failed and gives the error as could not continue scan with nolock due to data movement
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Is your `UPDATE` query updating data that's being read by another part of the stored proc? This is what I meant about providing the context of what's going wrong...
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Sure - I was wondering if there was some sort of CURSOR with a NOLOCK and an UPDATE in the middle of it all, like the [Halloween Problem][1]... hence my (repeated) requests for context! [1]: http://en.wikipedia.org/wiki/Halloween_Problem
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Oh, I guess I should have put the OPs name on that so it was clear who I was talking to. Apologies if that came off badly Thomas.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.