Date parsed: 19/10/2007 02:11:53
Date: Fri, 19 Oct 2007 09:11:53 -0700

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 -

Date parsed: 19/10/2007 03:56:44
Date: Fri, 19 Oct 2007 10:56:44 -0700

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 -