NOLOCK due to data movement

Question

NOLOCK due to data movement

sunny.tjk

SSCertifiable

Points: 5041
More actions
August 10, 2011 at 1:32 pm

#241946

I tried running this select query but it gave me the following error. It also gave me the same error when i tried it without nolock. The database is read only, not sure if that makes a difference.
select * from abc(nolock);
Error:
Server: Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can't be read only if you got that error => http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

Bottom line is don't use nolock unless you don't care about the accuracy results and getting your connection killed for no apparent reason.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

With a read only database no locks are taken anyway.

Please run this and post the results.

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Can you rephrase Gail? No sure your statement makes sense.

Do you mean that locks ar not taking place at all?

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 4

This database had been in read only mode for the last 6 months, so nothing would have changed since then. Not sure, why am I still getting this error.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

sunny.tjk (8/10/2011)
This database had been in read only mode for the last 6 months, so nothing would have changed since then. Not sure, why am I still getting this error.

Only 2 options, either it's read-only and corrupted like Gail is suggesting or it's not read-only and getting updated right now (or you're not running this on the correct server / db)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Ninja's_RGR'us (8/10/2011)
Can you rephrase Gail? No sure your statement makes sense.
Do you mean that locks ar not taking place at all?

A database that is in read only never opens any locks as no concurrency can change the data during the select transaction.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

That's what I knew, but it just read funny in my head, don't know why...

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Is it possible that your database is actually a database snapshot?

http://msdn.microsoft.com/en-us/library/ms189940.aspx

"A SELECT statement that is executed on a database snapshot must not specify a FILESTREAM column; otherwise, the following error message will be returned: Could not continue scan with NOLOCK due to data movement."

What is the result that you get from this query?

select

a.name,

compatibility_level,

user_access_desc,

state_desc,

source_database_id,

snapshot_isolation_state_desc,

is_read_committed_snapshot_on

from

sys.databases a

where

a.name = N'MyDBNameGoesHere'

order by

a.name

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 9

Michael Valentine Jones (8/10/2011)
Is it possible that your database is actually a database snapshot?
http://msdn.microsoft.com/en-us/library/ms189940.aspx
"A SELECT statement that is executed on a database snapshot must not specify a FILESTREAM column; otherwise, the following error message will be returned: Could not continue scan with NOLOCK due to data movement."
What is the result that you get from this query?
select
a.name,
compatibility_level,
user_access_desc,
state_desc,
source_database_id,
snapshot_isolation_state_desc,
is_read_committed_snapshot_on
from
sys.databases a
where
a.name = N'MyDBNameGoesHere'
order by
a.name

Here are the results:

ABC90MULTI_USERONLINENULLOFF0

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

sunny.tjk (8/10/2011)
Michael Valentine Jones (8/10/2011)
Is it possible that your database is actually a database snapshot?
http://msdn.microsoft.com/en-us/library/ms189940.aspx
"A SELECT statement that is executed on a database snapshot must not specify a FILESTREAM column; otherwise, the following error message will be returned: Could not continue scan with NOLOCK due to data movement."
What is the result that you get from this query?
select
a.name,
compatibility_level,
user_access_desc,
state_desc,
source_database_id,
snapshot_isolation_state_desc,
is_read_committed_snapshot_on
from
sys.databases a
where
a.name = N'MyDBNameGoesHere'
order by
a.name
Here are the results:
ABC90MULTI_USERONLINENULLOFF0

I left out a column. What do you get for this: