sql run by head blocker

Question

sql run by head blocker

mngong

SSCertifiable

Points: 6167
More actions
July 18, 2006 at 7:34 am

#61423

tried fn_get_sql and looked at other posts from this forum need to get this info and entire user sql .
What do I need to do so that the entire sql of head blocker is returned not only CMD .Recognize this will consume your time but tried for a while before posting.
SELECT p.spid SPID,
convert(char(12), d.name) DbName
, program_name Program_Name
, convert(char(12), l.name) Login_Name
, convert(char(12), hostname) HostName
, cmd CMD
, p.status Status
, login_time Login_Time
,waittime Wait_time
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid = d.dbid
JOIN      master..syslogins l ON p.sid = l.sid
WHERE     p.blocked = 0
AND       EXISTS ( SELECT *
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lance Sanders SSC Eights! Points: 903 More actions · Answer 1

DBCC INPUTBUFFER will give you a little more detail. I use something similar to this:

declare @handle binary(20), @spid int

select top 1 @spid = blocked
from master..sysprocesses a
where a.blocked != 0 
and a.blocked not in (select spid from master..sysprocesses b where blocked != 0)

if @spid is not null
begin
  select @handle = sql_handle from master..sysprocesses where spid = @spid

  exec sp_who2 @spid

  dbcc inputbuffer (@spid)

  select * from ::fn_get_sql(@handle)
end