1

Rather urgent one.

Have created an index on one of our tables and for some reason all data going into that table has stopped.

Can't see any blocking, but when I'm trying to drop or disable the index with either

ALTER INDEX [IX_DataCapture_Received] ON [queue].[queued_datacapture_data] DISABLE
DROP INDEX [IX_DataCapture_Received] ON [queue].[queued_datacapture_data] WITH ( ONLINE = OFF, MAXDOP = 1)

I get a report of deadlock preventing it, but nothing is being reported to sp_readerrorlog

Any clues as to what's going on?

  • Can you give us more details? Like what is the other query involved with the deadlock? What does "get a report" mean? – Aaron Bertrand Sep 8 '15 at 14:54
  • as far as I can tell its deadlocking against nothing, which makes no sense. – Ste Bov Sep 8 '15 at 14:54
  • 2
    Sadly, we can't fix "deadlocking against nothing." – Aaron Bertrand Sep 8 '15 at 14:55
  • 1
    Grab the sp whoIsActive from Adam Machanic, run it and see what is running on your box at the moment, this will help you to know what is blocking what, because as Aaron already commented, is impossible to have a "deadlock against nothing". – Yaroslav Sep 8 '15 at 15:01
1

Problem solved

Very hacky but got the system back

Set the deadlock priority to high to stop it being auto killed

there are a lot of external apps that access the DB which ended up blocking the drop index (information from the master..sysprocesses table) (but not reporting as blocking through all the checks we have), so I just killed until there was nothing more in its way, dropped straight away

cheers for the quick eyes over

  • 6
    Sorry, but that's not a fix – Aaron Bertrand Sep 8 '15 at 15:13
  • You should use SP_WHOISACTIVE. it will show everything its running on your instance. When you drop an index, the index needs to be blocked ( try it by GUI...it will say cant lock in time...or something like this). when I try do do this here ( yes i'm a DBA JR and I love to try things in my test environment ) I can see whats going on with whoisactive, and maybe kill the process that is blocking other sessions. You can either see SPID that is blocking other SPIDs. – Racer SQL Sep 22 '15 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.