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

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 statemen
t
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 advanceWonder wrote:
> 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 statem
ent
> 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
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.realsqlguy.com