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»»

NOLOCK due to data movement Expand / Collapse
Author
Message
Posted Wednesday, August 10, 2011 1:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:50 AM
Points: 299, Visits: 1,129
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.
Post #1157978
Posted Wednesday, August 10, 2011 1:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:23 AM
Points: 20,584, Visits: 9,624
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.
Post #1157980
Posted Wednesday, August 10, 2011 1:53 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
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 2008, MVP
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

Post #1158002
Posted Wednesday, August 10, 2011 1:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:23 AM
Points: 20,584, Visits: 9,624
Can you rephrase Gail? No sure your statement makes sense.

Do you mean that locks ar not taking place at all?
Post #1158006
Posted Wednesday, August 10, 2011 1:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:50 AM
Points: 299, Visits: 1,129
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.
Post #1158009
Posted Wednesday, August 10, 2011 2:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:23 AM
Points: 20,584, Visits: 9,624
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)
Post #1158016
Posted Wednesday, August 10, 2011 2:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:51 AM
Points: 5,446, Visits: 7,616
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1158018
Posted Wednesday, August 10, 2011 2:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:23 AM
Points: 20,584, Visits: 9,624
That's what I knew, but it just read funny in my head, don't know why...
Post #1158024
Posted Wednesday, August 10, 2011 2:16 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 3:34 PM
Points: 3,109, Visits: 11,516
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




Post #1158028
Posted Wednesday, August 10, 2011 2:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:50 AM
Points: 299, Visits: 1,129
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:
ABC 90 MULTI_USER ONLINE NULL OFF 0
Post #1158067
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse