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
*/
