Could not continue scan with NOLOCK

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Could not continue scan with NOLOCK

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2007-06-05 : 13:42:00

Hi,
I am getting the following error:
Server: Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.

However, on a different Server with the same database I am not getting this error.

Upon researching further on Microsoft web site for support, I found that this is a known bug?

I did Select @@version on both servers and this is what I got:

On working server:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

On 601 error server:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

This looks like OS version difference and not the SQL Server version difference or is it just me?

Please help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-05 : 15:12:23

yes it's the OS version diff.

can you show us what query are you running when getting this error?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-05 : 15:13:05

this occurs because you are reading with the NOLOCK hint (probably to sweep a deadlock under the rug?)

if someone comes along and does an insert or update that causes a page split and the data your dirty reader was reading gets moved to another page, you'll get this error.

Best is to not use the NOLOCK hint and fix the underlying issue that was causing the deadlock in the first place.

elsasoft.org

SQLCode
Posting Yak Master

143 Posts

Posted - 2007-06-05 : 15:35:13

This is a read only (warm stand by) mode. So there are no inserts to it. Also, I am not using the NOLOCK query hint. All the help I found online also suggested I remove the hint. But I am not using it??

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-05 : 15:48:44

can we see the query?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-05 : 16:22:42

also, if you have SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED that's equivalent to reading NOLOCK.

http://msdn2.microsoft.com/en-us/library/aa259216(SQL.80).aspx

elsasoft.org

SQLCode
Posting Yak Master

143 Posts

Posted - 2007-06-06 : 09:43:29

Query is very stright forward as given below. I also tried the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED yesterday, I will try again today since several transaction logs have been applied last night....

Select distinct DCode as [D C],DName as [D Name],XID as GMCLPID,LastName + ', '+FirstName as [Participant Name],
'('+ C.CNO + ') ' + C.CName as 'Crs',
Convert(Char(10),dStartDate,120) + ' ' + Convert(Char(5),dStartTime,108) + ' - ' + Convert(Char(10),dEndDate,120) + ' ' + Convert(Char(5),dEndTime,108) as 'Date Range',
TL as 'T L', 'No Show' as Status, 'n/a' as 'Cancelled By', 'n/a' as 'Cancel Date',
'n/a' as 'Comments'
from R
inner join CS on R.CSID = CS.CSID
inner join C on C.CID = CS.CID
inner join CC on C.CID = CC.CID
inner join A on R.AID = A.AID
inner join TC on TC.TCID = CS.TCID
inner join TL on TL.TL = TC.City
inner join LP on LP.PID=R.PID
inner join P on P.PID=LP.PID
inner join D on D.DID=LP.DID
where CC.CID = 12
and (A.AID = 3) --No SHOW
and LP.CID=12

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-06 : 10:11:26

sorry, let me be more clear: if you use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, that's the same as using NOLOCK in your query.

Both specify that you want to do dirty reads, which can result in the "Could not continue scan with NOLOCK due to data movement" error.

elsasoft.org

vuyiswamb
Starting Member

6 Posts

Posted - 2009-03-04 : 06:09:30

quote:
Originally posted by jezemine

sorry, let me be more clear: if you use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, that's the same as using NOLOCK in your query.

Both specify that you want to do dirty reads, which can result in the "Could not continue scan with NOLOCK due to data movement" error.

elsasoft.org

i have the same Problem, here is my SQL Statored Procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- Restore a backed up database
-- Given: the filename and path where the backedup database file is located and the name of the database to restore it as
-- The entry will be restored and a row placed into oDirect.dbo.tbl_dbref - which keeps track of the databases
-- The users for the database must also be restored!
ALTER PROCEDURE [dbo].[sp_RestoreDatabase]

@dbname char(32), -- the database name to restore as
@filename char(64), @path char(256) -- the location of the backuped up database file (on the SQL Server)

AS

set nocount on

declare @sql nvarchar(3000)

execute('sp_ClearDatabaseConnections ' + @dbname)

-- Restore the database
select @sql = ' RESTORE DATABASE ' + ltrim(rtrim( @dbname )) + ' FROM DISK = ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @filename )) + ''' '
select @sql = ltrim(rtrim(@sql)) + ' WITH RECOVERY, '
select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + 'TNGoedit_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , ' -- logical file name to physical name
select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + 'TNGoedit_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' ' -- logical file name to physical name
--select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + ltrim(rtrim(@dbname)) + '_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , ' -- logical file name to physical name
--select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + ltrim(rtrim(@dbname)) + '_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' ' -- logical file name to physical name
print @sql
execute ( @sql )

-- Was the command successful or was there a problem
if ( (select @@Error) = 0 ) begin
-- Put an entry into oDirect.dbo.tbl_dbRef
-- execute ( 'sp_DataSet_Save ''' + @xml + ''' ' )

-- TODO: restore the users

select 'Restore Successful' [Result]
end
else begin
select 'Restore Unsuccessful' [Result]
end

/* -- Example of a restore
RESTORE FILELISTONLY
FROM DISK = 'C:\Inetpub\wwwroot\oBooking\Databases\oBookingMasterDB'

RESTORE DATABASE tst
FROM DISK = 'C:\Inetpub\wwwroot\oBooking\Databases\oBookingMasterDB'
WITH RECOVERY,
MOVE 'LBS_Data' TO 'C:\Inetpub\wwwroot\oBooking\Databases\tst.mdf', -- logical file name to physical name
MOVE 'LBS_Log' TO 'C:\Inetpub\wwwroot\oBooking\Databases\tst_log.ldf' -- logical file name to physical name
*/

Do not be tolerent to ignorance but understanding illetaracy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources