question

vivekchandra09 avatar image
vivekchandra09 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?
deadlockdeveloperdrop
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
MAXKA avatar image
MAXKA answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image Wilfred van Dijk commented ·
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)
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.