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

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
-rockdaleDoes this KB article apply?
http://support.microsoft.com/kb/815008
Linchi
"rockdale" wrote:
> 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
>|||NOPE.
The database is SQL2K with SP4.
They did not execute any select statement, not mention Read Uncommited
option.
They execute the delete statement using one where condition and get
the error.
They execute the delete statement using another where condition and
no error occured.
I even execute the DBCC TRACEON (9134, -1) as the article you
mentioned, I still getting the same error. I can not restart the sql
server, it is a production server.
Thanks
On Oct 19, 11:46 am, Linchi Shea
<LinchiS...@.discussions.microsoft.com> wrote:
> Does this KB article apply?http://support.microsoft.com/kb/815008
> Linchi
>
> "rockdale" wrote:
> > 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- Hide quoted text -
> - Show quoted text -|||Look for a DELETE trigger on the table.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"rockdale" <rockdale.green@.gmail.com> wrote in message
news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
> 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
>|||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.
On Oct 19, 1:30 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Look for a DELETE trigger on the table.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "rockdale" <rockdale.gr...@.gmail.com> wrote in message
> news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
>
> > 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- Hide quoted text -
> - Show quoted text -|||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
On Oct 19, 1:30 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Look for a DELETE trigger on the table.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "rockdale" <rockdale.gr...@.gmail.com> wrote in message
> news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
>
> > 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- Hide quoted text -
> - Show quoted text -