October 3, 2013 at 6:17 am
Hi
SELECT wt.session_id, ot.task_state, wt.wait_type, wt.wait_duration_ms,
wt.blocking_session_id, wt.resource_description, es.[host_name],
es.[program_name] FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address
INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id
WHERE es.is_user_process = 1
this script provide only bocking SPID..
Pl give me, I want textdata (Query which query get it blocking) along with SPID.
thanks
ananda
October 3, 2013 at 6:53 am
This script provide query and SPID of blocking and SPID and blocked SPID.
and gives the blocking object name
SELECT
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
October 3, 2013 at 7:01 am
Hi,
This query will give two rows one is with running (blocking)
and other one is suspended (blocked)
SELECT r.session_id,
s.host_name,
s.login_name,
s.original_login_name,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
t.text as Query_Text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
October 3, 2013 at 8:31 am
create table #temp
(
spid smallint ,
ecid smallint ,
status nchar(30) ,
loginame nchar(128) ,
hostname nchar(128) ,
blk char(5) ,
dbname nchar(128) ,
cmd nchar(16),
request_id int
)
insert into #temp exec sp_who
select * from #temp where blk!=0 order by cast(blk as int) desc
October 3, 2013 at 11:57 pm
SrcName (10/3/2013)
create table #temp(
spid smallint ,
ecid smallint ,
status nchar(30) ,
loginame nchar(128) ,
hostname nchar(128) ,
blk char(5) ,
dbname nchar(128) ,
cmd nchar(16),
request_id int
)
insert into #temp exec sp_who
select * from #temp where blk!=0 order by cast(blk as int) desc
This will not give textdata (Query which query get it blocking)
October 4, 2013 at 12:40 am
parulprabu (10/3/2013)
SrcName (10/3/2013)
create table #temp(
spid smallint ,
ecid smallint ,
status nchar(30) ,
loginame nchar(128) ,
hostname nchar(128) ,
blk char(5) ,
dbname nchar(128) ,
cmd nchar(16),
request_id int
)
insert into #temp exec sp_who
select * from #temp where blk!=0 order by cast(blk as int) desc
This will not give textdata (Query which query get it blocking)
I have been used your way of getting blocked SPID, read data from DM-TABLE, but i think that is slower compared with sp_who.
I get sql text on this way
declare @sqltext VARBINARY(128)
select @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = @BLOCKED_SPID
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
Dear parulprabu
You may try and tell us opinion
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply