December 17, 2008 at 8:40 am
There are plenty of situations where we identify the queries causing the blocking locks and based on the query we get the table involved etc. In case where two or more tables are involved, how can we pin point this is the one table that causes blocking locks. Any queries... Any techniques... Any explanations...
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
December 17, 2008 at 8:47 am
Do you mean deadlocks? Blocking is the purpose of locks and while it should be minimized it is necessary for integrity.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 17, 2008 at 9:16 am
I did not mean deadlocks. I meant blocking locks. BTW, not all locks are blocking locks. A lock becomes blocking lock only when another process is looking for the same resource and the process holding the lock blocks it.
What i want to know how to identify the table that causes the blocking lock.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
December 17, 2008 at 10:03 am
There's plenty of script on the site to check for blocking and to return the information on the blocking spid, for example http://www.sqlservercentral.com/scripts/Lock+and+Connection+Management/30800/[/url] (you might want to remove the kill within this one).
There are others that make use of the cross apply syntax within 2005. Do a quick search on "blocking chain"
December 17, 2008 at 10:21 am
Justin (12/17/2008)
I did not mean deadlocks. I meant blocking locks. BTW, not all locks are blocking locks. A lock becomes blocking lock only when another process is looking for the same resource and the process holding the lock blocks it.What i want to know how to identify the table that causes the blocking lock.
Okay, I have seen many people think a block is a deadlock and they are 2 different things.
I would disagree a little bit on your definition of a blocking lock, mainly in semantics, because every lock is designed to be a blocking lock.
I think Nicholas has really pointed you in the right direction.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 18, 2008 at 12:45 am
Hi!
Here ...
... I have described how I deal with blocks & deadlocks; which processes I establish to monitor, analyze, etc. including several TSQL Code templates.
The examples given there are all related to Dynamics NAV, but I guess this does not really matter with this issue.
Hope this helps a little.
Kind regards,
Jörg
Jörg A. Stryk
MVP - MS Dynamics NAV
December 18, 2008 at 1:20 am
For that u have to run query
select * from sys.sysprocesses
when u run the qury, u will be find waitresources, it will be like 12:12344:778
From that u have to analize by DBName:Tablename(objectname)
so
select db_name(12)
select object_name(12344)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply