November 9, 2011 at 7:49 am
[font="Tahoma"]Hi all, im getting this error:
"Msg 601, Level 12, State 3, Line 470
Could not continue scan with NOLOCK due to data movement."
Im using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" at the begining of the query.
Can anyone help me?[/font]
November 9, 2011 at 7:51 am
What are you trying to do?
November 9, 2011 at 7:52 am
Nothing else to say. There was a page split and the data moved while you were reading the data. That's a fatal error where you'd need to rerun the transaction.
That's why you shouldn't use uncommitted if you care at all about correct results.
November 9, 2011 at 7:59 am
Im trying to make an INSERT on a temp table.
November 9, 2011 at 8:02 am
-Syd- (11/9/2011)
Im trying to make an INSERT on a temp table.
Which usually means that you read data from another table.
Changes nothing from your problem.
Any reasons why you think bad data is ok for this task?
November 9, 2011 at 8:03 am
November 9, 2011 at 8:06 am
-Syd- (11/9/2011)
[font="Tahoma"]Hi all, im getting this error:"Msg 601, Level 12, State 3, Line 470
Could not continue scan with NOLOCK due to data movement."
Im using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" at the begining of the query.
Can anyone help me?[/font]
Don't use Read uncommitted isolation level. That error can only occur when using READ UNCOMMITTED
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
November 9, 2011 at 8:19 am
The report was working correct!! Just went wrong a few minutes ago!
November 9, 2011 at 8:22 am
-Syd- (11/9/2011)
The report was working correct!! Just went wrong a few minutes ago!
It's an intermittant error.
You can be 100% certain it will happen again. It's just a matter of time.
November 9, 2011 at 8:30 am
Ninja's_RGR'us (11/9/2011)
-Syd- (11/9/2011)
The report was working correct!! Just went wrong a few minutes ago!It's an intermittant error.
You can be 100% certain it will happen again. It's just a matter of time.
Yup. It's a timing issue. It'll happen when the data is moving and you're reading. If you insist on read uncommitted, you'll have to add in retry logic to re-run the query when that error occurs.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply