I'm trying to drop an index that I have but when I execute the statement it just runs and runs (up to 30 mins before i stopped it last time). Has anyone ever run into this before? Any suggestions on how to free it up for deletion?
+2
A:
Do you have an idle transaction that holds a lock, perhaps?
Alex Brasetvik
2009-12-16 23:10:16
Hrrm how would I check?
Abe Miessler
2009-12-16 23:10:43
DBCC OPENTRAN will show open transactions. You could also pop open Activity Monitor, or run sp_Who2 and see who is blocking your drop.
Meff
2009-12-22 16:36:16
+2
A:
Use Adam Machanic's awesome WhoIsActive script to find out which other transaction is blocking your DROP statement, then wait for that to finish, ask the user to disconnect or just KILL the session.
doza
2009-12-16 23:17:55
That script is awesome. Thanks for pointing me to it. Found the blocking session. Can't kill it unfortunately but at least i know what's going on.
Abe Miessler
2009-12-16 23:25:31