Urgent:601 - Could not continue scan with NOLOCK due to data movem

Urgent:601 - Could not continue scan with NOLOCK due to data movem

Post by V29uZGV » Tue, 01 Aug 2006 22:47:02


Hi,

Lately, I have been getting this problem with a client. The query returns
around 6 thousand records, there is one user only and it's a select statement
with a temporary table in the join.

The server has a sp4 installed. It's a 2003 server.


CREATE TABLE #Temp (Temp_Id INT,
Temp_Id_Count INT NULL,
Temp_Id_Dt DATETIME NULL)

INSERT INTO #Temp (Temp_Id, Temp_Id_Count, Temp_Id_Dt)
SELECT T.Temp_Id, COUNT(TR.Temp_Id_Count), MAX(TR.Temp_Id_Dt)
FROM Test T WITH (NOLOCK)
LEFT JOIN Test_History TR WITH (NOLOCK) ON TR.Temp_Id = T.Temp_Id
WHERE T.Status_Cd = 1234
GROUP BY T.Temp_Id

SELECT DISTINCT T.Temp_Id, S.Client_Id, S.Subject_Id,
FROM SavedData S WITH (NOLOCK)
JOIN #Temp T ON T.Temp_Id = S.Temp_Id

The error after a few seconds is 601 - Could not continue scan with NOLOCK
due to data movement.

Any ideas?

Thanks in advance
 
 
 

Urgent:601 - Could not continue scan with NOLOCK due to data movem

Post by Tracy McKi » Tue, 01 Aug 2006 22:55:10


Why did you post this same question in the "server" newsgroup, under a
different username? Please don't do that, it wastes everyone's time
having to read your questions twice.


--
Tracy McKibben
MCDBA
http://www.yqcomputer.com/

 
 
 

Urgent:601 - Could not continue scan with NOLOCK due to data movem

Post by Erland Som » Tue, 01 Aug 2006 23:18:36

Wonder ( XXXX@XXXXX.COM ) writes:

If there is only one user, there is no reason to use NOLOCK is there?

NOLOCK is a gamble, and if you don't understand the exact implications
of NOLOCK, you should ont use it. Even if there is a single user on the
system, a reindexing or defragmentation job that runs simultaneously
could cause problems.



--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/