January 10, 2009 at 9:04 am
If a process / spid is blocking, what is the best way to query what tables are being locked by name?
When I see a spid blocking, I want to be able to say "spid x is blocking by holding resources from table y"
Thanks,
John
January 10, 2009 at 11:10 am
There may be a better way in 2005, because I am not familiar with all the DMV's, but sp_lock will show the resources locked and accepts a SPID as a parameter.
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
January 10, 2009 at 11:27 am
Hi!
Well, here - http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx - I describe how I deal with blocks & deadlocks, incl. several code examples.
(Have in min that I'm a NAV-developer and probably my TSQL is not that smart :blush:)
Maybe this could help you.
Kind regards,
Jörg
Jörg A. Stryk
MVP - MS Dynamics NAV
January 10, 2009 at 3:20 pm
John (1/10/2009)
If a process / spid is blocking, what is the best way to query what tables are being locked by name?When I see a spid blocking, I want to be able to say "spid x is blocking by holding resources from table y"
Well, IMHO, the best way is to use the Activity Monitor under the "Management" folder in SSMS. IT has the advantage of autmatically translating most of the resource/object id in the context of their own database. Almost every other (free) method, will require multiple steps to get there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 10:34 pm
Jorg, that link is very helpful. I was looking for a more general solution for 2000 but that's great for 2005. My big problem is interpretting the lock resources. Activity monitor is nice but sometimes it's not as quick to respond as I'd like.
A direct query is always nice. I'd like to take a blocked spid and see exactly what database / object name is being blocked. (or a blocker spid)
Thanks
John
January 14, 2009 at 1:01 am
You're welcome - I'm always glad if I could help 🙂
Jörg A. Stryk
MVP - MS Dynamics NAV
January 15, 2009 at 11:25 pm
John (1/10/2009)
If a process / spid is blocking, what is the best way to query what tables are being locked by name?When I see a spid blocking, I want to be able to say "spid x is blocking by holding resources from table y"
Thanks,
John
U can use following scripts:
-- tO FIND OUT WAITING PROCESS ------------
select ec.session_id,'dbcc inputbuffer ('+convert(varchar,ec.session_id)+')',st.text,
es.login_name,es.login_time,er.status,
er.start_time,datediff(minute,er.start_time,getdate()) as Diff_Min,er.command,er.database_id, db_name(er.database_id)as DBName,er.blocking_session_id,
er.wait_type,er.wait_time,er.last_wait_type,er.wait_resource,er.open_transaction_count,er.transaction_id,er.cpu_time,er.total_elapsed_time
from sys.dm_exec_connections ec
inner join sys.dm_exec_sessions es on (ec.session_id = es.session_id)
inner join sys.dm_exec_requests er on (ec.connection_id = er.connection_id)
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
-- TO FIND NODE PROCESS FOR BLOCKING -----------
1.
SELECT spid, status, loginame=substring(loginame, 1, 12),
hostname=substring(hostname, 1, 12),
blk=CONVERT(char(3), blocked),
open_tran,dbname=substring(db_name(dbid),1,10),cmd,
waittype, waittime, last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND blocked=0
--for 20005
2.
select r.session_id, r.blocking_session_id, r.database_id, r.command, s.last_request_start_time, s.login_name, r.last_wait_type, r.status
from sys.dm_exec_requests r
join sys.dm_exec_sessions s on r.session_id = s.session_id
where (r.blocking_session_id > 0 and r.blocking_session_id <> r.session_id)
or r.session_id in (select session_id from sys.dm_exec_requests where blocking_session_id > 0 and blocking_session_id <> session_id)
--for 2000
1.
select spid, blocked, dbid, cmd, last_batch, loginame, lastwaittype, status
from master..sysprocesses
where (blocked > 0 and blocked <> spid)
or spid in (select spid from master..sysprocesses where blocked > 0 and blocked <> spid)
January 16, 2009 at 6:36 am
None of these actually do what John asked Paresh.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 16, 2009 at 8:21 am
DBCC INPUTBUFFER(spid) will give you a hint what that spid is running. From there, you may find a clue.
January 16, 2009 at 10:21 pm
sunny Brook (1/16/2009)
DBCC INPUTBUFFER(spid) will give you a hint what that spid is running. From there, you may find a clue.
Yes, that i already posted..
January 18, 2009 at 5:38 pm
The deadlocks blocks is tough to graph but i would suggest using this tool
http://sqlsolutions.com/products/sql-deadlock-detector/index.html
I have used this and it is great.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply