set quoted_identifier off
go
use master
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_blockmonitor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_blockmonitor]
GO
create procedure usp_blockmonitor 
@latch int = 0,
@fast int = 1,
@Applicationlogpath varchar(600) = 'd:\BLOCKING.LOG'
as 
--Objective: To monitor blocking and write the output to a file
--created by: MAK
-- Date: Sep 25, 2004

set nocount on
declare @count int
declare @maxcount int
declare @waittype varchar(500)
declare @errstring varchar(1000)
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant
create table #dbccinputbuffer (eventtype varchar(100), parameter varchar(100), eventinfo varchar(7500))
create table #waitstats (id int identity(1,1),Waittype varchar(100), Requests decimal(25,5),Waittime decimal(25,5),signalwaittime decimal(25,5))
create table #opentran (id int identity(1,1),name varchar(100),opentran varchar(500))
declare @eventtype varchar(100)
declare @parameter varchar(100)
declare @eventinfo varchar(1000)
declare @query varchar(1000)
declare @opentran varchar(1000)
set @time = getdate()
declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
   ignore_app tinyint, primary key (blocked, spid, ecid))
insert @probclients select spid, ecid, blocked, waittype, dbid,
   case when convert(varchar(128),hostname) = 'PSSDIAG' then 1 else 0 end
   from sysprocesses (nolock) where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)
begin
   set @time2 = getdate()
set @errstring =''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 

set @errstring ='8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2))

   insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients
      where blocked not in (select spid from @probclients) and blocked != 0

   if (@fast = 1)
   begin
      set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      set @errstring = 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage, last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121),net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid,
         sql_handle, stmt_start, stmt_end
      from master..sysprocesses (nolock) 
      where blocked!=0 or waittype != 0x0000
         or spid in (select blocked from @probclients where blocked != 0)
         or spid in (select spid from @probclients where blocked != 0)

      set @errstring = 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      set @errstring = 'SYSPROC FIRST PASS'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      select spid, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         set @errstring = 'Blocking via locks at ' + convert(varchar(26), @time, 121)
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
         set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
         set @errstring = 'SPIDs at the head of blocking chains'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
         select spid from @probclients
            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
         if @latch = 0
         begin
            set @errstring = 'SYSLOCKINFO'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
            select @time2 = getdate()

            select spid = convert (smallint, req_spid),
               ecid = convert (smallint, req_ecid),
               rsc_dbid As dbid,
               rsc_objid As ObjId,
               rsc_indid As IndId,
               Type = case rsc_type when 1 then 'NUL'
                                    when 2 then 'DB'
                                    when 3 then 'FIL'
                                    when 4 then 'IDX'
                                    when 5 then 'TAB'
                                    when 6 then 'PAG'
                                    when 7 then 'KEY'
                                    when 8 then 'EXT'
                                    when 9 then 'RID'
                                    when 10 then 'APP' end,
               Resource = substring (rsc_text, 1, 16),
               Mode = case req_mode + 1 when 1 then NULL
                                        when 2 then 'Sch-S'
                                        when 3 then 'Sch-M'
                                        when 4 then 'S'
                                        when 5 then 'U'
                                        when 6 then 'X'
                                        when 7 then 'IS'
                                        when 8 then 'IU'
                                        when 9 then 'IX'
                                        when 10 then 'SIU'
                                        when 11 then 'SIX'
                                        when 12 then 'UIX'
                                        when 13 then 'BU'
                                        when 14 then 'RangeS-S'
                                        when 15 then 'RangeS-U'
                                        when 16 then 'RangeIn-Null'
                                        when 17 then 'RangeIn-S'
                                        when 18 then 'RangeIn-U'
                                        when 19 then 'RangeIn-X'
                                        when 20 then 'RangeX-S'
                                        when 21 then 'RangeX-U'
                                        when 22 then 'RangeX-X'end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end,
               req_transactionID As TransID, req_transactionUOW As TransUOW
            from master.dbo.syslockinfo  s (nolock) ,
               @probclients p
            where p.spid = s.req_spid

            set @errstring = 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
         end -- latch not set
      end
      else
         set @errstring = 'No blocking via locks at ' + convert(varchar(26), @time, 121)
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
   end  -- fast set

   else  
   begin  -- Fast not set
      set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      set @errstring = 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage, last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121),net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid,
         sql_handle, stmt_start, stmt_end
      from master..sysprocesses (nolock) 

      set @errstring = 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 

      set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      set @errstring = 'SYSPROC FIRST PASS'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      select spid, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         set @errstring = 'Blocking via locks at ' + convert(varchar(26), @time, 121)
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
         set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
         set @errstring = 'SPIDs at the head of blocking chains'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
         select spid from @probclients
         where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
         if @latch = 0
         begin
            set @errstring = 'SYSLOCKINFO'

            select @time2 = getdate()

            select spid = convert (smallint, req_spid),
               ecid = convert (smallint, req_ecid),
               rsc_dbid As dbid,
               rsc_objid As ObjId,
               rsc_indid As IndId,
               Type = case rsc_type when 1 then 'NUL'
                                    when 2 then 'DB'
                                    when 3 then 'FIL'
                                    when 4 then 'IDX'
                                    when 5 then 'TAB'
                                    when 6 then 'PAG'
                                    when 7 then 'KEY'
                                    when 8 then 'EXT'
                                    when 9 then 'RID'
                                    when 10 then 'APP' end,
               Resource = substring (rsc_text, 1, 16),
               Mode = case req_mode + 1 when 1 then NULL
                                        when 2 then 'Sch-S'
                                        when 3 then 'Sch-M'
                                        when 4 then 'S'
                                        when 5 then 'U'
                                        when 6 then 'X'
                                        when 7 then 'IS'
                                        when 8 then 'IU'
                                        when 9 then 'IX'
                                        when 10 then 'SIU'
                                        when 11 then 'SIX'
                                        when 12 then 'UIX'
                                        when 13 then 'BU'
                                        when 14 then 'RangeS-S'
                                        when 15 then 'RangeS-U'
                                        when 16 then 'RangeIn-Null'
                                        when 17 then 'RangeIn-S'
                                        when 18 then 'RangeIn-U'
                                        when 19 then 'RangeIn-X'
                                        when 20 then 'RangeX-S'
                                        when 21 then 'RangeX-U'
                                        when 22 then 'RangeX-X'end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end,
               req_transactionID As TransID, req_transactionUOW As TransUOW
            from master.dbo.syslockinfo  (nolock) 

            set @errstring = 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
         end -- latch not set
      end
      else
        set @errstring = 'No blocking via locks at ' + convert(varchar(26), @time, 121)
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
   end -- Fast not set

   set @errstring = 'DBCC SQLPERF(WAITSTATS)'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 

set @query = '  dbcc sqlperf(waitstats)'
truncate table #waitstats
insert #waitstats (Waittype ,Requests ,Waittime ,signalwaittime )
exec (@query)

set @maxcount = scope_identity()
set @count =1
while @count <=@maxcount
begin

select @waittype =convert(varchar(100),waittype )+','+
convert(varchar(100),requests)+','+convert(varchar(100),waittime)+','+convert(varchar(100),signalwaittime)
from 
#waitstats where id = @count

EXEC master..Usp_WriteToFile @Applicationlogpath,@waittype 
set @count = @count+1
end


   set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
   set @errstring = '*********************************************************************'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
   set @errstring = 'Print out DBCC Input buffer for all blocked or blocking spids.'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
   set @errstring = '*********************************************************************'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 

   declare ibuffer cursor fast_forward for
   select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked
   from @probclients
   where (spid <> @@spid) and 
      ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
      or spid in (select blocked from @probclients where blocked != 0))
   open ibuffer
   fetch next from ibuffer into @spid, @blocked
   while (@@fetch_status != -1)
   begin
      set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      set @errstring = 'DBCC INPUTBUFFER FOR SPID ' + @spid
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
--      exec ('dbcc inputbuffer (' + @spid + ')')
set @query = 'dbcc inputbuffer('+@spid+')'
truncate table #dbccinputbuffer 
insert #dbccinputbuffer(eventtype ,parameter , eventinfo ) exec (@Query)
select @eventtype =eventtype, @parameter=parameter,@eventinfo=left(eventinfo,900)
from #dbccinputbuffer
EXEC master..Usp_WriteToFile @Applicationlogpath, @eventtype
EXEC master..Usp_WriteToFile @Applicationlogpath, @parameter 
EXEC master..Usp_WriteToFile @Applicationlogpath, @eventinfo 
--eventtype varchar(100), parameter varchar(100), eventinfo varchar(1000)
--select * from #dbccinputbuffer 

      fetch next from ibuffer into @spid, @blocked
   end
   deallocate ibuffer

   set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
   set @errstring = '*******************************************************************************'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
   set @errstring = 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
   set @errstring = '*******************************************************************************'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
   declare ibuffer cursor fast_forward for
   select distinct cast (dbid as varchar(6)) from @probclients
   where dbid != 0
   open ibuffer
   fetch next from ibuffer into @spid
   while (@@fetch_status != -1)
   begin
      set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      set @dbname = db_name(@spid)
      set @status = DATABASEPROPERTYEX(@dbname,'Status')
      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')
      set @errstring = 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      if @Status = N'ONLINE' and @UserAccess != N'SINGLE_USER'
begin
--dbcc opentran (test)
set @query = 'dbcc opentran('+@dbname+') with tableresults, NO_INFOMSGS'
--truncate table #opentran
--set @query = 'dbcc opentran(test) with tableresults, NO_INFOMSGS'
truncate table #opentran
insert #opentran (name,opentran) exec (@query)
--select  * from #opentran 
--insert #opentran exec (@query)
set @maxcount =scope_identity()
set @count =1
while @count<=@maxcount
begin
select  @opentran = name+','+opentran from #opentran  where id = @count
EXEC master..Usp_WriteToFile @Applicationlogpath, @opentran
set @count = @count+1
end
end
--dbcc opentran(@dbname)
--dbcc opentran (TEST)
      else
begin
         set @errstring = 'Skipped: Status=' + convert(nvarchar(128),@status)
            + ' UserAccess=' + convert(nvarchar(128),@useraccess)

EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
end
      set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      if @spid = '2' select @blocked = 'Y'
      fetch next from ibuffer into @spid
   end
   deallocate ibuffer
   if @blocked != 'Y' 
   begin
      set @errstring = ''
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
      set @errstring = 'DBCC OPENTRAN FOR DBID  2 [tempdb]'
--      dbcc opentran ('tempdb')
set @query = 'dbcc opentran(tempdb)'

truncate table #opentran
insert #opentran (name,opentran) exec (@query)
--select  * from #opentran 
--insert #opentran exec (@query)
set @maxcount =scope_identity()
set @count =1
while @count<=@maxcount
begin
select  @opentran = name+','+opentran from #opentran  where id = @count
EXEC master..Usp_WriteToFile @Applicationlogpath, @opentran
set @count = @count+1
end

   end

   set @errstring = 'End time: ' + convert(varchar(26), getdate(), 121)
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
end -- All
else
  set @errstring = '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' ' 
     + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)')
EXEC master..Usp_WriteToFile @Applicationlogpath, @errstring 
GO