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?