April 9, 2014 at 8:11 am
Hi,
I am getting the below error in prodution
ODBC SQL Server Driver][SQL Server]Could not continue scan with NOLOCK due to data movement
unable to resolve.
If anyone faced the issue with error can you show the path how did you resolve them?
Thanks in advance.!
April 9, 2014 at 8:15 am
Simple, don't use NOLOCK.
April 9, 2014 at 8:16 am
What is the version of SQL you are running and if you can post the query you ran .
April 9, 2014 at 8:21 am
Lynn Pettis (4/9/2014)
Simple, don't use NOLOCK.
What is the cause of the problem ?
I have been using this statement for nearly 6 years in prodution i did not get any error or issue.
all of the sudden it happened today and i am unable to resolve the issue, still searching for the reason.
April 9, 2014 at 8:22 am
Lynn Pettis (4/9/2014)
Simple, don't use NOLOCK.
+10000
OP, do you know the issues with using that hint? Is missing and/or duplicate data acceptable? Because when using NOLOCK both of those things will happen at some point.
Here are 3 excellent articles explaining the pitfalls of this hint.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2014 at 8:46 am
You're experiencing page splits or rearrangement due to changes in the data and you're using, I suspect, a client-side cursor to retrieve the information. This is actually normal behavior. You've just been lucky to not run into it before (or your users have hit it multiple times and just not reported it, happens all the time). My best suggestions are, in order:
1) Stop using NOLOCK for all the reasons stated above
2) Stop using a client side cursor. There are better ways to retrieve the data through ODBC.
3) Move the cursor, if you must have one, to a server-side cursor. You might still hit the issue, but maybe not. Server-side cursors are generally faster (if still not good).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2014 at 8:55 am
yuvipoy (4/9/2014)
Lynn Pettis (4/9/2014)
Simple, don't use NOLOCK.What is the cause of the problem ?
I have been using this statement for nearly 6 years in prodution i did not get any error or issue.
all of the sudden it happened today and i am unable to resolve the issue, still searching for the reason.
The cause of the problem is that you've been using a bad solution for nearly six years instead of attacking the root cause.
April 9, 2014 at 10:35 am
yuvipoy (4/9/2014)
Lynn Pettis (4/9/2014)
Simple, don't use NOLOCK.What is the cause of the problem ?
The nolock hint.
I have been using this statement for nearly 6 years in prodution i did not get any error or issue.
Then you've been lucky. It's a documented effect of running in the read uncommitted isolation level. No, it doesn't happen every time you use it, but it can happen. Rerun the query or take out the hint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply