Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Blocking SPID with Text Data? Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 6:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 5:56 AM
Points: 1,068, Visits: 3,033
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
Post #1501147
Posted Thursday, October 3, 2013 6:53 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
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



Post #1501164
Posted Thursday, October 3, 2013 7:01 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
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

Post #1501167
Posted Thursday, October 3, 2013 8:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 2, 2014 3:14 PM
Points: 108, Visits: 338
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
Post #1501221
Posted Thursday, October 3, 2013 11:57 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
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)
Post #1501427
Posted Friday, October 4, 2013 12:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 2, 2014 3:14 PM
Points: 108, Visits: 338
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
Post #1501435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse