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

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

Post by jorgpint » Tue, 01 Aug 2006 22:43:57


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 movement.

Post by Tracy McKi » Tue, 01 Aug 2006 22:53:43


That error means that the data that your query was working with has
changed in the underlying tables. By using NOLOCK, you're allowing
other processes to change the same data that you're attempting to
perform aggregations on. I suspect you threw NOLOCK in there to "solve"
a performance problem (i.e. your query blocks everything else in the
system). Get rid of NOLOCK, and fix the query.

Is the code you posted the complete query? If so, WHY are you
calculating COUNT and MAX, stuffing those into a temp table, and then
never using those values? You could accomplish everything you have
written here with a single SELECT statement.



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

 
 
 

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

Post by jorgpint » Tue, 01 Aug 2006 23:12:31

The problem is there is only one user in the system. The other
processes would not change the status, since it's one to one in this
case.

In my work environment it works fine. The problem started lately with
new implementations maybe of SP 3a?


Yes, the query is not completed. This was just basically example of
what I have, the commands and so on.

Thanks in advance.