2

I have a large and busy production database and I'm trying to tidy up the indexes that have accumulated over the years and reclaim some space. But I'm unable to drop/disable the existing indexes. They're all non-clustered yet disable fails with the below error and drop locks the table so I end up cancelling the statement after running for 5minutes.

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

Any ideas? I'm sure I'm missing something very obvious but I was under the impression non-clustered indexes are metadata only so should be quick and easy to drop.

ETA - Follow up question - Is there any way I can lock the table to Management Studio manually so the drop run quicker?

TT.
  • 15,774
  • 6
  • 47
  • 88
Code Ninja
  • 727
  • 1
  • 7
  • 9

1 Answers1

0

You didn't mention RDBMS version, but assuming it is MS SQL SERVER 2012, DROP indexes ONLINE without locks on the table are allowed only for clustered indexes as in here.

You're trying to drop an index but someone is using the table, so the LOCK TIMEOUT happens. I think you can drop the indexes only without someone accessing the relying table:

Table locks are applied and the table is unavailable for the duration of the index operation.

adamitj
  • 350
  • 2
  • 14