-
Dropping a clustered index hangs a SP?
I've got a process that runs nightly that loads a fair amount of data into some "staging" tables [in a SQL 7 database]. After the data is loaded into the staging tables, I run a proc that creates indexes (clustered and non) on the staging tables. I then update production tables. As the last step in my job, I drop all of the indexes that I created.
Unfortunately, the part that drops all my indexes is failing. It gets to a particular [clustered] index and then hangs. The SP never finishes, causing me to reset the job daily (very bad.)
Here's the statement I use to create the index:
if not exists(select * from sysindexes where name = 'PK_ECL_1'
CREATE CLUSTERED INDEX PK_ECL_1 ON dbo.pt_equity_composite_load(gmc, secType, symbol, sid)
Here's the drop statement:
if exists(select * from sysindexes where name = 'PK_ECL_1'
DROP INDEX PT_EQUITY_COMPOSITE_LOAD.PK_ECL_1
The results of the drop:
The clustered index has been dropped.
Index (ID = 2) is being rebuilt.
Index (ID = 3) is being rebuilt.
Index (ID = 4) is being rebuilt.
Index (ID = 5) is being rebuilt.
Index (ID = 6) is being rebuilt.
Index (ID = 7) is being rebuilt.
Index (ID = 8) is being rebuilt.
Index (ID = 9) is being rebuilt.
I find the results of the drop suspicious. Are all non-clustered indexes rebuilt when I drop the clustered index? Any insight/suggestions as to why this would be failing would be appreciated.
TIA
Brian
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|