use master
go

if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss') and sysstat & 0xf = 4)
   drop procedure dbo.sp_blocker_pss
GO

create proc sp_blocker_pss (@fast int = 0)
as 
set nocount on
declare @spid varchar(6), @uid varchar(6), @blocked varchar(6)
declare @tmpchar varchar(255)
declare @time datetime

select @spid = count(*) from master..sysprocesses where blocked!=0 or waittype != 0x0000
if @spid > 0
begin
   select @time = getdate()
   select @tmpchar='Start time: ' + convert(varchar(26), @time, 113)
   print @tmpchar
   print ' '
   if (@fast = 1)
   begin
      select spid, status, blocked, cmd, lastwaittype,
         open_tran,waitresource, waittype, waittime, cpu, physical_io,
         memusage,last_batch=convert(varchar(26), last_batch,113),
         login_time=convert(varchar(26), login_time,113), net_address,
         net_library,dbid, ecid, kpid, hostname,hostprocess,
         loginame,program_name, nt_domain, nt_username, uid, sid
      from master..sysprocesses
      where blocked!=0 or waittype != 0x0000 or spid in (select blocked from master..sysprocesses where blocked != 0)

      Print 'Blocking Lock Information:'
      Print '=========================='
      Print''
      select convert (smallint, req_spid) As spid, 
         rsc_dbid As dbid,
         rsc_objid As ObjId,
         rsc_indid As IndId,
         substring (v.name, 1, 4) As Type,    
         substring (rsc_text, 1, 16) as Resource,
         substring (u.name, 1, 8) As Mode,
         substring (x.name, 1, 5) As Status
      from master.dbo.syslockinfo s,
         master.dbo.spt_values v,
         master.dbo.spt_values x,
         master.dbo.spt_values u
      where s.rsc_type = v.number
         and v.type = 'LR'
         and s.req_status = x.number
         and x.type = 'LS'
         and s.req_mode + 1 = u.number
         and u.type = 'L'
         and s.rsc_text in (select rsc_text from syslockinfo where req_status <> 1)
         and s.req_status = 1
      --order by spid

      Print''
      Print 'Waiting Lock Information:'
      Print '=========================='
      Print''
      select    convert (smallint, req_spid) As spid, 
         rsc_dbid As dbid,
         rsc_objid As ObjId,
         rsc_indid As IndId,
         substring (v.name, 1, 4) As Type,
         substring (rsc_text, 1, 16) as Resource,
         substring (u.name, 1, 8) As Mode,
         substring (x.name, 1, 5) As Status
      from master.dbo.syslockinfo s,
         master.dbo.spt_values v,
         master.dbo.spt_values x,
         master.dbo.spt_values u
      where   s.rsc_type = v.number
         and v.type = 'LR'
         and s.req_status = x.number
         and x.type = 'LS'
         and s.req_mode + 1 = u.number
         and u.type = 'L'
         and s.req_status <> 1
   --  order by spid
   end  -- fast set

   else  
   begin  -- Fast not set
      select spid, status, blocked, cmd, lastwaittype, open_tran, 
      waitresource, waittype, waittime, cpu, physical_io, 
      memusage, last_batch=convert(varchar(26), last_batch,113), login_time=convert(varchar(26),login_time,113), 
      net_address, net_library,  dbid, ecid, kpid, hostname, hostprocess, loginame,
      program_name, nt_domain, nt_username, uid, sid
      from master..sysprocesses

      print ''
      print 'SPIDs at the head of blocking chains'
      select spid from master..sysprocesses 
      where blocked = 0 and spid in (select blocked from master..sysprocesses where spid != 0)
      print ''

      exec sp_lock
    
   end -- Fast not set

   dbcc traceon(3604)
   Print ''
   Print ''
   Print '*********************************************************************'
   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
   Print 'Print out DBCC PSS info only for SPIDs at the head of blocking chains'
   Print '*********************************************************************'

   declare ibuffer insensitive cursor for
   select cast (spid as varchar(6)) as spid, cast (uid as varchar(6)) as uid, cast (blocked as varchar(6)) as blocked
   from master..sysprocesses 
   where (spid <> @@spid) and (blocked!=0 or waittype != 0x0000 or spid in (select blocked from master..sysprocesses where blocked != 0))
   open ibuffer
   fetch next from ibuffer into @spid, @uid, @blocked
   while (@@fetch_status != -1)
   begin
      print ''
      print ''
      exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''')
      exec ('dbcc inputbuffer (' + @spid + ')')
      print ''
      if (@blocked = '0')
      -- if DBCC PSS is not required comment the line above, remove the     -- comment on the line below and run the stored procedure in fast mode
      -- if (@blocked = '0' and @fast = 0)
      begin
         exec ('print ''DBCC PSS FOR SPID ' + @spid + '''')
         exec ('dbcc pss (' + @uid + ', ' + @spid + ')')
         print ''
         print ''
      end
      fetch next from ibuffer into @spid, @uid, @blocked
   end
   deallocate ibuffer

   if datediff(millisecond, @time, getdate()) > 1000
   begin
      select @tmpchar='End time: ' + convert(varchar(26), @time, 113)
      print @tmpchar
   end
 
   dbcc traceoff(3604)

end -- All
go 

This page was last updated on May 01, 2006 04:28 PM.