601 - Could not continue scan with NOLOCK due to data movement.

601 - Could not continue scan with NOLOCK due to data movement.

Post by V29uZGV » Sun, 30 Jul 2006 04:36:01


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
 
 
 

601 - Could not continue scan with NOLOCK due to data movement.

Post by Tracy McKi » Sun, 30 Jul 2006 04:42:16


That means data that your query is using has changed while your query
was running. That's one risk of using NOLOCK, you run the risk of
reading data that is in the process of being modified.


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