Msg 601 - bizarre

Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a reportin
g
database that I load each night with a SSIS package - PK's are dropped then
readded at the end - I am getting the strangest error rebuilding one PK on
one table - I have tested adding the PK thru the GUI with the same error -
all the doc on this error suggests it comes from using hints to change
isolation levels running queries, but I am simply trying to add a Primary
Key. Anyone seen this or ahve any ideas? Thanks so much, I've commented ou
t
the offending line for now but would like to get the PK back on this table -
I also ran a clean DBCC checktable. here is the statement and the error -
(Select a.name from sysobjects a where a.xtype = 'PK'
and a.name = 'PK_drug_test') Alter Table [dbo].[drug_test] ADD
Constraint PK_drug_test Primary Key Clustered
(
[test_id],
[lcd_id]
) ON [PRIMARY]
Msg 601, Level 12, State 3, Line 2
Could not continue scan with NOLOCK due to data movement.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
The statement has been terminated.
John F.Hi
Did you change the Isolation level in the Tools/Options/Query Execution/SQL
Server
or Query/Query Options/Advanced settings in Managerment Studio? If not then
you may want to check what is being set by using SQL Profiler.
John
"John F." wrote:

> Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a report
ing
> database that I load each night with a SSIS package - PK's are dropped the
n
> readded at the end - I am getting the strangest error rebuilding one PK on
> one table - I have tested adding the PK thru the GUI with the same error -
> all the doc on this error suggests it comes from using hints to change
> isolation levels running queries, but I am simply trying to add a Primary
> Key. Anyone seen this or ahve any ideas? Thanks so much, I've commented
out
> the offending line for now but would like to get the PK back on this table
-
> I also ran a clean DBCC checktable. here is the statement and the error -
> (Select a.name from sysobjects a where a.xtype = 'PK'
> and a.name = 'PK_drug_test') Alter Table [dbo].[drug_test] ADD
> Constraint PK_drug_test Primary Key Clustered
> (
> [test_id],
> [lcd_id]
> ) ON [PRIMARY]
> Msg 601, Level 12, State 3, Line 2
> Could not continue scan with NOLOCK due to data movement.
> Msg 1750, Level 16, State 0, Line 2
> Could not create constraint. See previous errors.
> The statement has been terminated.
> --
> John F.|||"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> Hi - we are on SQL 2005 and I jsut recently applied SP1. We have a
> reporting
> database that I load each night with a SSIS package - PK's are dropped
> then
> readded at the end -
You shouldn't be dropping and readding clustered PK's. You _might_ want to
drop non-clustered indexes and rebuild them at the end. But loading the
data into a page heap and then moving all the data pages into a clustered
index is unlikely to be faster than loading the data into the clustered
index to begin with. And since it completely recreates the table, you might
as well load into a staging table.
Also in SQL 2005 instead of dropping secondary indexes, you can just disable
them and rebuild them later. This is much easier to script and manage you
don't have to hard-code all the index definitions in the load script.
David|||we did extensive testing and leaving the PK's (clustered or non) made the
SSIS load come to a complete crawl as it tried to maintain/update them - we
do a complete refresh every time so we are a bit of a different scenario.
Back to the original issue - the isolation level is not being changed at all
during this and the error doesn't seem to match the activity at all.
--
John F.
"David Browne" wrote:

> "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> You shouldn't be dropping and readding clustered PK's. You _might_ want t
o
> drop non-clustered indexes and rebuild them at the end. But loading the
> data into a page heap and then moving all the data pages into a clustered
> index is unlikely to be faster than loading the data into the clustered
> index to begin with. And since it completely recreates the table, you mig
ht
> as well load into a staging table.
> Also in SQL 2005 instead of dropping secondary indexes, you can just disab
le
> them and rebuild them later. This is much easier to script and manage you
> don't have to hard-code all the index definitions in the load script.
> David
>
>|||Hi John
You may want to try explicitly setting the ONLINE option to OFF and the
MAXDOP option to 1.
John
"John F." wrote:
[vbcol=seagreen]
> we did extensive testing and leaving the PK's (clustered or non) made the
> SSIS load come to a complete crawl as it tried to maintain/update them - w
e
> do a complete refresh every time so we are a bit of a different scenario.
> Back to the original issue - the isolation level is not being changed at a
ll
> during this and the error doesn't seem to match the activity at all.
> --
> John F.
>
> "David Browne" wrote:
>|||hey, thanks to everyone for the help - I will try explicitly setting the
ONLINE to off and the MAXDOP to 1. FYI if it makes any diff this is a 64 bi
t
server jsut recently updated to SP1. David in regards to your comment, I am
changing the previous developer's code to build the PK's as non-clustered as
well. Is there a better way to disable them to load the tons of data we loa
d
every night and then re-enable? we reaplce all data for any given district
when we receive their files, but the other data in the database stays the
same until those corresponding districts are rec'd.
We are getting a few strange erros on this server suddenly - may not be
related but last night my maint plan ran out of stack space on and update
statistics as well.
Thanks again for the help, guys.
--
John F.
"David Browne" wrote:

> "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> news:EF5E716D-5896-4207-96F6-F9928EDCA403@.microsoft.com...
> You shouldn't be dropping and readding clustered PK's. You _might_ want t
o
> drop non-clustered indexes and rebuild them at the end. But loading the
> data into a page heap and then moving all the data pages into a clustered
> index is unlikely to be faster than loading the data into the clustered
> index to begin with. And since it completely recreates the table, you mig
ht
> as well load into a staging table.
> Also in SQL 2005 instead of dropping secondary indexes, you can just disab
le
> them and rebuild them later. This is much easier to script and manage you
> don't have to hard-code all the index definitions in the load script.
> David
>
>|||John, that worked! Thanks so much - I would've never figured out to
exlpicitly set MAXDOP on a PK rebuild, wonder why it works for awhile withou
t
those options set and then starts throwing the error though? So weird.
Thanks again!
--
John F.
"John Bell" wrote:
[vbcol=seagreen]
> Hi John
> You may want to try explicitly setting the ONLINE option to OFF and the
> MAXDOP option to 1.
> John
> "John F." wrote:
>|||John - that worked! Thanks so much for your help, the PK rebuilt just fine.
We started getting the error on another table too so I guess I can change
them to nonclustered and then add those two options for all - wouldve never
thought to explicitly set the degree of parallelism - wonder why it works w/
o
those options for awhile and then starts failing throwing the error all of a
sudden? anyway thanks again!
--
John F.
"John Bell" wrote:
[vbcol=seagreen]
> Hi John
> You may want to try explicitly setting the ONLINE option to OFF and the
> MAXDOP option to 1.
> John
> "John F." wrote:
>|||Hi
I guess it depends on how much work the rebuild is estimated to take and/or
if it thinks it can do parts in parallel. I guess it thought wrong!! You may
want to log this at [url]http://lab.msdn.microsoft.com/productfeedback/default.aspx[/ur
l]
John
"John F." wrote:
[vbcol=seagreen]
> John, that worked! Thanks so much - I would've never figured out to
> exlpicitly set MAXDOP on a PK rebuild, wonder why it works for awhile with
out
> those options set and then starts throwing the error though? So weird.
> Thanks again!
> --
> John F.
>
> "John Bell" wrote:
>|||I will go log it - FYI I can take off the options you suggest and run the PK
nonclustered just fine as well, so it may be more of an issue of shuffling a
huge amount of data around on disk after the fact - this is running late at
night so I dont think its fighting with other processes. So the answer is
either build them nonclustered or if building clustered use the MAXDOP=1 and
ONLINE=NO options - thanks again.
--
John F.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I guess it depends on how much work the rebuild is estimated to take and/o
r
> if it thinks it can do parts in parallel. I guess it thought wrong!! You m
ay
> want to log this at [url]http://lab.msdn.microsoft.com/productfeedback/default.aspx[/
url]
> John
> "John F." wrote:
>