Post reply

Drop Index

  • April 22, 2010 at 5:48 am

    #99135

    I have a table with 19 million rows and it's the Key table to our whole system.

    I'm trying to drop one of many indexes. We've out grown many of the beginning ones.

    I've let it run for over 2 minutes, but nothing happens. I've stopped it multiple times wondering why it is taking so long.

    I have a backup of the DB and it drops the index immediately.

    My question is, does the drop index need exclusive access to the table to be able to drop the index?

    How long should I let it run before getting worried?

  • April 22, 2010 at 5:58 am

    #1156003

    It depends...

    Is the index clustered or nonclustered? It makes a huge difference.

    -- Gianluca Sartori

  • April 22, 2010 at 6:22 am

    #1156018

    Thanks for you reply.

    It is a non-clustered indexes.

    Here is the create statement:

    CREATE INDEX [Idx_SourceID] ON [dbo].[Tablename]([SourceID]) WITH FILLFACTOR = 90 ON [PRIMARY]

    I'm using this statement to drop it:

    drop index [dbo].[Tablename].[Idx_SourceID]

  • April 22, 2010 at 6:30 am

    #1156024

    A nonclustered index should drop very quickly (should be metadata only).

    I found this in BOL, maybe it could be helpful, even if it comes from SQL2008 BOL.

    I'm sorry, this is all I can tell you. Let's see if somebody with better understanding of this topic chimes in.

    -- Gianluca Sartori

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply