Send email when Blocking Happens and Track Queries

 --This script will send an email to operator if blocking process counter rise above 0

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Blocking',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=120,
@include_event_description_in=0,
@database_name=N'',
@notification_message=N'',
@event_description_keyword=N'',
@performance_condition=N'SQLServer:General Statistics|Processes blocked||>|0',
@wmi_namespace=N'',
@wmi_query=N'',
@job_id=N'401a9e4d-ae94-47ca-9ac4-deb4c244690a'
GO

-----------------------------JOB NAME "Blocking Job"
 --------------------when blocking happens to notify what script blocking , we can have the below script run to keep track of blocking transaction
--CREATE TABLE AuditBlocking
--    (
--      session_id INT ,
--      Databasename VARCHAR(100) ,
--      DatetimeEvent DATETIME NOT NULL
--                             DEFAULT ( GETDATE() ) ,
--      SQL_text VARCHAR(MAX) ,
--      blocking_session_id INT ,
--      wait_time INT ,
--      wait_type VARCHAR(100) ,
--      last_wait_type VARCHAR(100) ,
--      wait_resource VARCHAR(100) ,
--      transaction_isolation_level INT ,
--      lock_timeout INT
--    )
--GO


      WAITFOR DELAY '00:01:00';
      IF EXISTS ( SELECT    session_id ,
                            DB_NAME(database_id) AS 'Database_Name' ,
                            GETDATE() ,
                            sql_text.text AS 'Text' ,
                            blocking_session_id ,
                            wait_time --Milliseconds(1000 miliosec=1 sec) ,
                            wait_type ,
                            last_wait_type ,
                            wait_resource ,
                            transaction_isolation_level ,
                            ( er.wait_time / 1000 ) ,--added this
                            lock_timeout
                  FROM      sys.dm_exec_requests er
                            CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
                  WHERE     ( er.wait_time / 1000 ) > 60 --added this
)
        BEGIN

            INSERT  INTO AuditBlocking---i have created this table before
                    SELECT  session_id ,
                            DB_NAME(database_id) AS 'Database_Name' ,
                            GETDATE() ,
                            sql_text.text AS 'Text' ,
                            blocking_session_id ,
                            wait_time --Milliseconds(1000 miliosec=1 sec) ,
                            wait_type ,
                            last_wait_type ,
                            wait_resource ,
                            transaction_isolation_level ,
                            ( er.wait_time / 1000 ) ,--added this
                            lock_timeout
                    FROM    sys.dm_exec_requests er
                            CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text
                    WHERE   ( er.wait_time / 1000 ) > 60;  --added this


            DECLARE @AlertMessage VARCHAR(MAX);
            SET @AlertMessage = 'There is Blocking happening on' + ' '
                + @@SERVERNAME + ' '
                + 'Server. Please see AuditDB.dbo.AuditBlocking Table for more Information';
            EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',
                @recipients = 'abc@xyz.com', @body = @AlertMessage,
                @importance = 'Low', @subject = 'Blocking Alert';

        END; 




-----------------------------TESTING
USE AuditDB
GO
CREATE TABLE ##Employees
    (
      EmpId INT IDENTITY ,
      EmpName VARCHAR(16) ,
      Phone VARCHAR(16)
    )
GO
INSERT  INTO ##Employees
        (EmpName,Phone )
VALUES  ('Martha','800-555-1212'),
        ('Jimmy','619-555-8080')
GO

CREATE TABLE ##Suppliers
    (
      SupplierId INT IDENTITY ,
      SupplierName VARCHAR(64) ,
      Fax VARCHAR(16)
    )
GO
INSERT  INTO ##Suppliers
        ( SupplierName, Fax )
VALUES  ('Acme', '877-555-6060' ),
        ('Rockwell', '800-257-1234' )
GO

USE AuditDB
GO
BEGIN TRANSACTION
SELECT  *
FROM    ##Employees WITH ( TABLOCKX, HOLDLOCK );
WAITFOR DELAY '00:02:00'
 ---Wait a minute!
ROLLBACK TRANSACTION
--Release the lock



 ----another query window
 SELECT *
 FROM   ##Employees


 --------------------------check results

 SELECT * FROM dbo.AuditBlocking
 ORDER BY DatetimeEvent asc

SELECT * FROM AuditBlocking
--WHERE   blocking_session_id>=50


/*testing
 SELECT * FROM AuditBlocking
 SP_who2 'Active'

--SELECT * FROM sys.messages
--WHERE message_id IN (823,824,825) AND language_id=1033

--SELECT * FROM sys.messages
--WHERE severity IN (016,017,018,019,020,021,022,023,024,025) AND language_id=1033
*/