Blocking Monitor and alert

I have created following blocking monitor which will monitor and alert if threshold is crossed. We can pass the parameters when the alert to be triggered (Number of blockings and number of seconds the blocking persists).
I have configured a SQL job which runs every 5 minutes and alert accordingly.

CREATE proc dbo.sp_dba_Blocking_Alert      
   @email_list varchar(255) = 'MYEMAIL@DOMAIN.COM' , 
   @blocker_threshold smallint = 1,         
   @wait_time_threshold_sec int = 5      
/*        
 --Description      
 -- This procedure will monitor for blocks, sends a mail if the it meets the criteria       
 -- Sample is picked from http://www.sqlservercentral.com/articles/Monitoring/monitoringblocks/1709/ and corrected, modified to meet the requirements      
       
 --Input parameters      
 --@email_list varchar(255) - Mail Address         
 --@blocker_threshold smallint  - Send mail only if the number of blocks is greater or equal to the parameter, 1 is default      
 --@wait_time_threshold_sec int = 5 -- consider it as blocking only if the blocking exists for X seconds       
      
       
    --Example      
    --  exec sp_dba_Blocking_Alert      
 --  exec sp_dba_Blocking_Alert @email_list='myalias@domain.com', @blocker_threshold=2, @wait_time_threshold_sec=8      
 --  exec sp_dba_Blocking_Alert 'myalias@domain.com', 2, 8      
      
 --   Additional Comments      
    --  sp_send_dbmail is used to send the mail and default profile is used       
           
 --   Author      
    --  Vijred       
       
 --   Created      
 --  2014-04-23      
       
    --Modifications      
    --  None      
*/      
as      
BEGIN       
      
set nocount on       
      
Declare @total_blockers smallint, @waittime int      
 ,  @maxid int, @minid int      
declare @blocker_spid smallint, @blocker_spid_info varchar(500)       
 , @blocker_spid_msg varchar(7000)       
 declare @tbl_blockers table (blocker_spid int,tid int identity(1,1))       
      
select @total_blockers = count(*)       
 from master..sysprocesses       
 where blocked > 0 and waittime > @wait_time_threshold_sec * 1000      
      
set @blocker_spid = 0      
set @blocker_spid_info = ''      
set @blocker_spid_msg = CAST(@total_blockers as char(3)) +   'process(es) are blocked. Blocking info on ' + convert(varchar(19),@@servername) + ' at '+ rtrim(convert(varchar(19),getdate(),121)) + Char(13) + Char(13)       
      
IF (@total_blockers >= @blocker_threshold )                    
-- if there are blockers      
 BEGIN      
   insert into @tbl_blockers (blocker_spid)      
     select spid from master..sysprocesses       
     where blocked > 0 and waittime > @wait_time_threshold_sec * 1000       
     union       
     select blocked from master..sysprocesses      
   where blocked > 0 and waittime > @wait_time_threshold_sec * 1000      
      
     select @minid = min(tid), @maxid = max(tid) from @tbl_blockers       
      
    WHILE ( @minid <= @maxid )       
     BEGIN      
     -- begin while loop       
       if ( @total_blockers >= @blocker_threshold )      
        BEGIN       
          select @blocker_spid = blocker_spid       
    from @tbl_blockers      
          where tid = @minid       
                              
          select @blocker_spid_info =  'Block on ' +  rtrim(@@servername) + ',  SPID='+      
            rtrim(convert(varchar(6),p.spid))+',  bloked by '+      
    isNull(cast(blocked as varchar(9)), ' ') + ',  '+       
            rtrim(convert(varchar(15),IsNull(p.status, ' ')))+' ,  '+                           
            rtrim(convert(varchar(25),IsNull(p.loginame, ' ')))+' , '+                           
            rtrim(convert(varchar(15),IsNull(p.hostname, ' ')))+' , '+                           
            rtrim(convert(varchar(30),IsNull(p.program_name, ' ')))+' , '+                           
            rtrim(convert(varchar(25),IsNull(p.cmd, ' ')))+', login_time='+      
            rtrim(convert(varchar(19),IsNull(p.login_time,'1900-01-01'),121))+', last_batch='+      
            rtrim(convert(varchar(19),IsNull(p.last_batch,'1900-01-01'),121)) + ', waittime(sec)=' +      
   rtrim(convert(varchar(12),p.waittime/1000)) + ' ' + Char(13)+ Char(13)      
           from master..sysprocesses p      
           where p.spid = @blocker_spid      
        end       
           
      set @blocker_spid_msg = @blocker_spid_msg + @blocker_spid_info      
      set @minid = @minid + 1      
    END                      
    -- end while loop       
               
 EXEC msdb.dbo.sp_send_dbmail      
 -- @profile_name  = 'MY_MAIL_PROFILE',    -- Include Profile Name if there is no default profile
 @body_format = 'TEXT',      
 @recipients = @email_list,      
 @body = @blocker_spid_msg,      
 @subject = 'Blocking Process on Server'  
      
 END
END 

-Vijred

Advertisements
This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s