Could not continue scan with NOLOCK due to data movement. when delete a record

Could not continue scan with NOLOCK due to data movement. when delete a record

Post by rockdal » Sun, 21 Oct 2007 00:27:49


Error 601
Severity Level 12
Message Text
Could not continue scan with NOLOCK due to data movement.

My client always hit this error when he tried to delete a record from
table. The table contains about 1 million records. the table has a
unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
(col_a),
non-unique index idx_3 on col_c. An update trigger on table also.

When he execute
delete from table_a where col_a = @parm1 and col_c = @parm3. he gets
the 601 error.
but if he execute
delete from table_a where col_a = @parm1 and col_b = @parm2. he can
delete this one record.

We execute dbcc checktable(table_a) and no error reported.

I read posts that saying it is because read-uncommitted or nolock
hint, but we do not has this options and we did not execute select
statement.

Any idea why this happens, we are afraid that the table is not healthy/
waiting to corruption. Is there anything we can do to narrow down
where the cause is and to provent it happen again?

and it is MS SQL 2000 with SP4

thanks in advance
-rockdale
 
 
 

Could not continue scan with NOLOCK due to data movement. when delete a record

Post by Geoff N. H » Sun, 21 Oct 2007 02:30:50

Look for a DELETE trigger on the table.

--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP

 
 
 

Could not continue scan with NOLOCK due to data movement. when delete a record

Post by rockdal » Sun, 21 Oct 2007 02:44:16

They have a n update trigger in the table basically update the
lastedUpdateDateTime field. The Trigger is for Update only, so I do
not think delete will trigger this trigger.
 
 
 

Could not continue scan with NOLOCK due to data movement. when delete a record

Post by rockdal » Sun, 21 Oct 2007 02:56:44

I just delete the trigger and I still getting the same error.

More Info on index:
They have a UNIQUE NONCLUSTERED Constraints on table_a
(col_a, col_b, col_c)

an INDEX on table_a
(col_c)

an INDEX on table_a
(col_a)

Might these indices problems?

thanks