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 -

Could not continue scan with NOLOCK due to data movement.

I received the error message
Could not continue scan with NOLOCK due to data movement.
when I run the following SQL
update statistics PCR_Value with fullscan
I don't get any error if I run
update statistics PCR_Value with sample 75 percent
or any percentage smaller than 75. But if I go over 75 percent, then I get
the "Could not continue scan with NOLOCK due to data movement." I've run
dbcc checktable and receive no errors. Any suggestions?
Try running the stats update during a period of no/low activity. To improve
performance I would guess that process does not take a table lock and thus
can get 'confusled' when it tries to hit data that has changed underneath of
it.
Also, if you REALLY need to make it happen, what about opening a
transaction, doing a select top 1 * using an exclusive table lock hint,
running the update stats in the same tran, then committing the tran? Not
sure if the update stats would even be allowed to run there though. NOTE
that you would lock out ALL access to the table during the time this was
going on, even if it took hours!!
TheSQLGuru
President
Indicium Resources, Inc.
"Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
>I received the error message
> Could not continue scan with NOLOCK due to data movement.
> when I run the following SQL
> update statistics PCR_Value with fullscan
> I don't get any error if I run
> update statistics PCR_Value with sample 75 percent
> or any percentage smaller than 75. But if I go over 75 percent, then I
> get
> the "Could not continue scan with NOLOCK due to data movement." I've run
> dbcc checktable and receive no errors. Any suggestions?
|||Kevin,
Thanks for the advice. My main concern about getting this error is what
caused it. Updating statistics of large tables in the database is part of a
server installation program and our installer made sure that all of the
applications on the server were terminated before the installation program
started. We have repeated this process twice on the server and encountered
the same error. The same migration process was executed for users at other
sites with a lot more rows in the same table and the process ran without
error. Could this be a SQL bug for MS SQL 2000?
"Kevin G. Boles" wrote:

> Try running the stats update during a period of no/low activity. To improve
> performance I would guess that process does not take a table lock and thus
> can get 'confusled' when it tries to hit data that has changed underneath of
> it.
> Also, if you REALLY need to make it happen, what about opening a
> transaction, doing a select top 1 * using an exclusive table lock hint,
> running the update stats in the same tran, then committing the tran? Not
> sure if the update stats would even be allowed to run there though. NOTE
> that you would lock out ALL access to the table during the time this was
> going on, even if it took hours!!
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
> "Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
> news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
>
>
|||I suppose it could be a bug somewhere. But I think it is caused due to some
form of DML during data read operations. Are you absolutely certain there
was NO DML activity (even from your own package/installer thing) concurrent?
Are you also certain it was an update statistics run?
See http://support.microsoft.com/kb/815008 for a known bug which returns
this error message. Perhaps that will help.
TheSQLGuru
President
Indicium Resources, Inc.
"Ching-ju Lee" <ChingjuLee@.discussions.microsoft.com> wrote in message
news:4E41D7B0-66E0-4B6D-9F15-1BDAC9C7EAF1@.microsoft.com...[vbcol=seagreen]
> Kevin,
> Thanks for the advice. My main concern about getting this error is what
> caused it. Updating statistics of large tables in the database is part of
> a
> server installation program and our installer made sure that all of the
> applications on the server were terminated before the installation program
> started. We have repeated this process twice on the server and
> encountered
> the same error. The same migration process was executed for users at
> other
> sites with a lot more rows in the same table and the process ran without
> error. Could this be a SQL bug for MS SQL 2000?
> "Kevin G. Boles" wrote:
|||I am pretty sure the UPDATE STATISTICS with full scan caused the error
message because I can reproduce the exact error using ISQL on my test machine
when I restored the database from the user's site. I can eliminate the error
if I run UPDATE STATISTICS with 75 or lower percent. I read the article
before. The resolution was to install the latest service pack. The user,
and my test machine both have SQL 2000 SP4 installed. So still not sure what
caused the problem.
"Kevin G. Boles" wrote:

> I suppose it could be a bug somewhere. But I think it is caused due to some
> form of DML during data read operations. Are you absolutely certain there
> was NO DML activity (even from your own package/installer thing) concurrent?
> Are you also certain it was an update statistics run?
> See http://support.microsoft.com/kb/815008 for a known bug which returns
> this error message. Perhaps that will help.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>

Could not continue scan with NOLOCK due to data movement.

I received the error message
Could not continue scan with NOLOCK due to data movement.
when I run the following SQL
update statistics PCR_Value with fullscan
I don't get any error if I run
update statistics PCR_Value with sample 75 percent
or any percentage smaller than 75. But if I go over 75 percent, then I get
the "Could not continue scan with NOLOCK due to data movement." I've run
dbcc checktable and receive no errors. Any suggestions?Try running the stats update during a period of no/low activity. To improve
performance I would guess that process does not take a table lock and thus
can get 'confusled' when it tries to hit data that has changed underneath of
it.
Also, if you REALLY need to make it happen, what about opening a
transaction, doing a select top 1 * using an exclusive table lock hint,
running the update stats in the same tran, then committing the tran? Not
sure if the update stats would even be allowed to run there though. NOTE
that you would lock out ALL access to the table during the time this was
going on, even if it took hours!!
TheSQLGuru
President
Indicium Resources, Inc.
"Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
>I received the error message
> Could not continue scan with NOLOCK due to data movement.
> when I run the following SQL
> update statistics PCR_Value with fullscan
> I don't get any error if I run
> update statistics PCR_Value with sample 75 percent
> or any percentage smaller than 75. But if I go over 75 percent, then I
> get
> the "Could not continue scan with NOLOCK due to data movement." I've run
> dbcc checktable and receive no errors. Any suggestions?|||Kevin,
Thanks for the advice. My main concern about getting this error is what
caused it. Updating statistics of large tables in the database is part of a
server installation program and our installer made sure that all of the
applications on the server were terminated before the installation program
started. We have repeated this process twice on the server and encountered
the same error. The same migration process was executed for users at other
sites with a lot more rows in the same table and the process ran without
error. Could this be a SQL bug for MS SQL 2000?
"Kevin G. Boles" wrote:
> Try running the stats update during a period of no/low activity. To improve
> performance I would guess that process does not take a table lock and thus
> can get 'confusled' when it tries to hit data that has changed underneath of
> it.
> Also, if you REALLY need to make it happen, what about opening a
> transaction, doing a select top 1 * using an exclusive table lock hint,
> running the update stats in the same tran, then committing the tran? Not
> sure if the update stats would even be allowed to run there though. NOTE
> that you would lock out ALL access to the table during the time this was
> going on, even if it took hours!!
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
> "Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
> news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
> >I received the error message
> > Could not continue scan with NOLOCK due to data movement.
> >
> > when I run the following SQL
> > update statistics PCR_Value with fullscan
> >
> > I don't get any error if I run
> > update statistics PCR_Value with sample 75 percent
> > or any percentage smaller than 75. But if I go over 75 percent, then I
> > get
> > the "Could not continue scan with NOLOCK due to data movement." I've run
> > dbcc checktable and receive no errors. Any suggestions?
>
>|||I suppose it could be a bug somewhere. But I think it is caused due to some
form of DML during data read operations. Are you absolutely certain there
was NO DML activity (even from your own package/installer thing) concurrent?
Are you also certain it was an update statistics run?
See http://support.microsoft.com/kb/815008 for a known bug which returns
this error message. Perhaps that will help.
TheSQLGuru
President
Indicium Resources, Inc.
"Ching-ju Lee" <ChingjuLee@.discussions.microsoft.com> wrote in message
news:4E41D7B0-66E0-4B6D-9F15-1BDAC9C7EAF1@.microsoft.com...
> Kevin,
> Thanks for the advice. My main concern about getting this error is what
> caused it. Updating statistics of large tables in the database is part of
> a
> server installation program and our installer made sure that all of the
> applications on the server were terminated before the installation program
> started. We have repeated this process twice on the server and
> encountered
> the same error. The same migration process was executed for users at
> other
> sites with a lot more rows in the same table and the process ran without
> error. Could this be a SQL bug for MS SQL 2000?
> "Kevin G. Boles" wrote:
>> Try running the stats update during a period of no/low activity. To
>> improve
>> performance I would guess that process does not take a table lock and
>> thus
>> can get 'confusled' when it tries to hit data that has changed underneath
>> of
>> it.
>> Also, if you REALLY need to make it happen, what about opening a
>> transaction, doing a select top 1 * using an exclusive table lock hint,
>> running the update stats in the same tran, then committing the tran? Not
>> sure if the update stats would even be allowed to run there though. NOTE
>> that you would lock out ALL access to the table during the time this was
>> going on, even if it took hours!!
>>
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>>
>> "Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
>> news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
>> >I received the error message
>> > Could not continue scan with NOLOCK due to data movement.
>> >
>> > when I run the following SQL
>> > update statistics PCR_Value with fullscan
>> >
>> > I don't get any error if I run
>> > update statistics PCR_Value with sample 75 percent
>> > or any percentage smaller than 75. But if I go over 75 percent, then I
>> > get
>> > the "Could not continue scan with NOLOCK due to data movement." I've
>> > run
>> > dbcc checktable and receive no errors. Any suggestions?
>>|||I am pretty sure the UPDATE STATISTICS with full scan caused the error
message because I can reproduce the exact error using ISQL on my test machine
when I restored the database from the user's site. I can eliminate the error
if I run UPDATE STATISTICS with 75 or lower percent. I read the article
before. The resolution was to install the latest service pack. The user,
and my test machine both have SQL 2000 SP4 installed. So still not sure what
caused the problem.
"Kevin G. Boles" wrote:
> I suppose it could be a bug somewhere. But I think it is caused due to some
> form of DML during data read operations. Are you absolutely certain there
> was NO DML activity (even from your own package/installer thing) concurrent?
> Are you also certain it was an update statistics run?
> See http://support.microsoft.com/kb/815008 for a known bug which returns
> this error message. Perhaps that will help.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>

Could not continue scan with NOLOCK due to data movement.

I received the error message
Could not continue scan with NOLOCK due to data movement.
when I run the following SQL
update statistics PCR_Value with fullscan
I don't get any error if I run
update statistics PCR_Value with sample 75 percent
or any percentage smaller than 75. But if I go over 75 percent, then I get
the "Could not continue scan with NOLOCK due to data movement." I've run
dbcc checktable and receive no errors. Any suggestions?Try running the stats update during a period of no/low activity. To improve
performance I would guess that process does not take a table lock and thus
can get 'confusled' when it tries to hit data that has changed underneath of
it.
Also, if you REALLY need to make it happen, what about opening a
transaction, doing a select top 1 * using an exclusive table lock hint,
running the update stats in the same tran, then committing the tran? Not
sure if the update stats would even be allowed to run there though. NOTE
that you would lock out ALL access to the table during the time this was
going on, even if it took hours!!
TheSQLGuru
President
Indicium Resources, Inc.
"Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
>I received the error message
> Could not continue scan with NOLOCK due to data movement.
> when I run the following SQL
> update statistics PCR_Value with fullscan
> I don't get any error if I run
> update statistics PCR_Value with sample 75 percent
> or any percentage smaller than 75. But if I go over 75 percent, then I
> get
> the "Could not continue scan with NOLOCK due to data movement." I've run
> dbcc checktable and receive no errors. Any suggestions?|||Kevin,
Thanks for the advice. My main concern about getting this error is what
caused it. Updating statistics of large tables in the database is part of a
server installation program and our installer made sure that all of the
applications on the server were terminated before the installation program
started. We have repeated this process twice on the server and encountered
the same error. The same migration process was executed for users at other
sites with a lot more rows in the same table and the process ran without
error. Could this be a SQL bug for MS SQL 2000?
"Kevin G. Boles" wrote:

> Try running the stats update during a period of no/low activity. To impro
ve
> performance I would guess that process does not take a table lock and thus
> can get 'confusled' when it tries to hit data that has changed underneath
of
> it.
> Also, if you REALLY need to make it happen, what about opening a
> transaction, doing a select top 1 * using an exclusive table lock hint,
> running the update stats in the same tran, then committing the tran? Not
> sure if the update stats would even be allowed to run there though. NOTE
> that you would lock out ALL access to the table during the time this was
> going on, even if it took hours!!
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
> "Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
> news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
>
>|||I suppose it could be a bug somewhere. But I think it is caused due to some
form of DML during data read operations. Are you absolutely certain there
was NO DML activity (even from your own package/installer thing) concurrent?
Are you also certain it was an update statistics run?
See http://support.microsoft.com/kb/815008 for a known bug which returns
this error message. Perhaps that will help.
TheSQLGuru
President
Indicium Resources, Inc.
"Ching-ju Lee" <ChingjuLee@.discussions.microsoft.com> wrote in message
news:4E41D7B0-66E0-4B6D-9F15-1BDAC9C7EAF1@.microsoft.com...[vbcol=seagreen]
> Kevin,
> Thanks for the advice. My main concern about getting this error is what
> caused it. Updating statistics of large tables in the database is part of
> a
> server installation program and our installer made sure that all of the
> applications on the server were terminated before the installation program
> started. We have repeated this process twice on the server and
> encountered
> the same error. The same migration process was executed for users at
> other
> sites with a lot more rows in the same table and the process ran without
> error. Could this be a SQL bug for MS SQL 2000?
> "Kevin G. Boles" wrote:
>|||I am pretty sure the UPDATE STATISTICS with full scan caused the error
message because I can reproduce the exact error using ISQL on my test machin
e
when I restored the database from the user's site. I can eliminate the erro
r
if I run UPDATE STATISTICS with 75 or lower percent. I read the article
before. The resolution was to install the latest service pack. The user,
and my test machine both have SQL 2000 SP4 installed. So still not sure wha
t
caused the problem.
"Kevin G. Boles" wrote:

> I suppose it could be a bug somewhere. But I think it is caused due to so
me
> form of DML during data read operations. Are you absolutely certain there
> was NO DML activity (even from your own package/installer thing) concurren
t?
> Are you also certain it was an update statistics run?
> See http://support.microsoft.com/kb/815008 for a known bug which returns
> this error message. Perhaps that will help.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>

Could not continue scan with NOLOCK due to data movement

I have a client that is continually getting this error message:
Could not continue scan with NOLOCK due to data movement
Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
Document they patched the server
http://support.microsoft.com/default.aspx/kb/815008.
Even after patching, they are continuing to get the error.
The article also mentions a trace flag, but I don't think that flag
should apply. Our software is not trying to "READ UNCOMMITTED"
isolation levels, we are providing a WITH NO LOCK optimizing hint on
the SQL statement.
Any help would be appreciated. If you need additional information, let
me know.
Thank you!
That is usually due to the fact they are running in Read Uncommitted
isolation level or are using the NOLOCK hint. If they are reading a page /
row and another user deletes that row or splits that page they get hosed.
This is usually rare unless you do a lot of scanning which typically
indicates lack of proper tuning and indexes.
Andrew J. Kelly SQL MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegro ups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>
|||Read Uncommitted and WITH NOLOCK are equivalent. WITH NOLOCK sets the
isolation level for the individual query while READ UNCOMMITTED does the
same thing for either the connection or the entire database, depending on
where it is used. You are likely using a very active transactional system
to support an analysis type query. The underlying transactional changes
cause the large aggregations or intermediate result sets to fail. You can
try using a replicated or log shipped copy as your source or upgrade to SQL
2005 and try using Snapshot isolation.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegro ups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>

Could not continue scan with NOLOCK due to data movement

I have a client that is continually getting this error message:
Could not continue scan with NOLOCK due to data movement
Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
Document they patched the server
http://support.microsoft.com/default.aspx/kb/815008.
Even after patching, they are continuing to get the error.
The article also mentions a trace flag, but I don't think that flag
should apply. Our software is not trying to "READ UNCOMMITTED"
isolation levels, we are providing a WITH NO LOCK optimizing hint on
the SQL statement.
Any help would be appreciated. If you need additional information, let
me know.
Thank you!That is usually due to the fact they are running in Read Uncommitted
isolation level or are using the NOLOCK hint. If they are reading a page /
row and another user deletes that row or splits that page they get hosed.
This is usually rare unless you do a lot of scanning which typically
indicates lack of proper tuning and indexes.
Andrew J. Kelly SQL MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>|||Read Uncommitted and WITH NOLOCK are equivalent. WITH NOLOCK sets the
isolation level for the individual query while READ UNCOMMITTED does the
same thing for either the connection or the entire database, depending on
where it is used. You are likely using a very active transactional system
to support an analysis type query. The underlying transactional changes
cause the large aggregations or intermediate result sets to fail. You can
try using a replicated or log shipped copy as your source or upgrade to SQL
2005 and try using Snapshot isolation.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>

Could not continue scan with NOLOCK due to data movement

I have a client that is continually getting this error message:
Could not continue scan with NOLOCK due to data movement
Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
Document they patched the server
http://support.microsoft.com/default.aspx/kb/815008.
Even after patching, they are continuing to get the error.
The article also mentions a trace flag, but I don't think that flag
should apply. Our software is not trying to "READ UNCOMMITTED"
isolation levels, we are providing a WITH NO LOCK optimizing hint on
the SQL statement.
Any help would be appreciated. If you need additional information, let
me know.
Thank you!That is usually due to the fact they are running in Read Uncommitted
isolation level or are using the NOLOCK hint. If they are reading a page /
row and another user deletes that row or splits that page they get hosed.
This is usually rare unless you do a lot of scanning which typically
indicates lack of proper tuning and indexes.
--
Andrew J. Kelly SQL MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>|||Read Uncommitted and WITH NOLOCK are equivalent. WITH NOLOCK sets the
isolation level for the individual query while READ UNCOMMITTED does the
same thing for either the connection or the entire database, depending on
where it is used. You are likely using a very active transactional system
to support an analysis type query. The underlying transactional changes
cause the large aggregations or intermediate result sets to fail. You can
try using a replicated or log shipped copy as your source or upgrade to SQL
2005 and try using Snapshot isolation.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>I have a client that is continually getting this error message:
> Could not continue scan with NOLOCK due to data movement
> Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
> Document they patched the server
> http://support.microsoft.com/default.aspx/kb/815008.
> Even after patching, they are continuing to get the error.
> The article also mentions a trace flag, but I don't think that flag
> should apply. Our software is not trying to "READ UNCOMMITTED"
> isolation levels, we are providing a WITH NO LOCK optimizing hint on
> the SQL statement.
> Any help would be appreciated. If you need additional information, let
> me know.
> Thank you!
>