I created a non-clustered index on a live server (SQL Server 2008 R2) and it turned out not to give the performance benefits that I was hoping for. So I want to delete the index again.
Unfortunately I cannot get a lock on the index as it is in use. It's probably always going to be in use. Restarting SQL Server is not really an option.
I've also had this same problem in the past when I've wanted to rebuild an index, and I've had to work around the issue by reorganising instead (which is sometimes very inefficient).
Is there any way to tell SQL Server to stop using an index? Or is it possible to run a query that will attempt to obtain an exclusive lock on the index, wait indefinitely, and then delete the index? Any other options? Or do I just have to wait until there's some kind of outage and then hope I remember to remove it before everything comes back online?