asked
Take long time to drop a view
It took one of my developer 1 minute and 58 seconds. Where do I start looking if this is a deadlock issue?
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
It may not be a deadlock issue. What you need to do is, start looking for blocking. Its better you run SP_who2 active and find if you're drop statement is getting blocked. Once you find the process of blocking as an SPID, see if that Process ID can be KILLED.
tip: instead of sp_who2, have a look at sp_whoisactive from Adam Machanic (
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx)
You can't drop an object with active queries being run against. Each of those queries is holding a schema lock on the object while the query runs so that things don't change in the middle of the query. Once the queries are finished, then you can drop the object. As was previously stated, this is a blocking issue. One process is preventing another process from accessing a resource. This is absolutely not a deadlock. A deadlock is what is known as a deadly embrace. Process A is holding resource X and needs resource Y to complete. Process B is holding resource Y and needs resource X to complete. Neither can win. One is chosen as a deadlock victim, the process is automatically killed, and there's an error stating this fact. If you didn't get a deadlock error and were just waiting, you're dealing with blocking, not deadlocks.
16 People are following this question.
