USE [Tools]
GO
/****** Object: StoredProcedure [dbo].[BlockedSPAlert1] Script Date: 11/27/2023
2:47:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BlockedSPAlert1]
AS
BEGIN
-- Check for blocked queries
DECLARE @rowcount INT
SELECT @rowcount = COUNT(1)
FROM [sys].[dm_exec_requests] req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
INNER JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE [blocking_session_id] <> 0 AND wait_time > 80000
-- If there are blocked queries
IF @rowcount > 0
BEGIN
-- Create a table to store blocked queries permanently
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'PermanentBlockedQueries')
BEGIN
CREATE TABLE dbo.PermanentBlockedQueries (
ID INT IDENTITY(1, 1),
SessionID INT,
BlockingSessionID INT,
ProcedureName NVARCHAR(255),
Status NVARCHAR(50),
WaitTime INT,
WaitType NVARCHAR(255),
WaitResource NVARCHAR(MAX),
TransactionID INT,
LockTimeout INT,
LogDate DATETIME DEFAULT GETDATE()
)
END
-- Insert blocked queries into the permanent table
INSERT INTO dbo.PermanentBlockedQueries (
SessionID,
BlockingSessionID,
ProcedureName,
Status,
WaitTime,
WaitType,
WaitResource,
TransactionID,
LockTimeout
)
SELECT
req.session_id,
blocking_session_id,
ib.event_info,
req.status,
wait_time,
wait_type,
wait_resource,
transaction_id,
req.lock_timeout
FROM [sys].[dm_exec_requests] req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
INNER JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE [blocking_session_id] <> 0 AND wait_time > 80000
ORDER BY wait_time DESC
-- Generate HTML for the email body
DECLARE @tableHTML NVARCHAR(MAX)
SET @tableHTML =
N'Hello;<br/><br/> On the SQL server, queries are blocked for more than 2
minutes:<br/>' +
N'<table style="border-collapse:collapse;font-size:10pt;white-space:nowrap;"
border="1" cellpadding="10">' +
N'<tr
bgcolor="#D6D9E0"><td>session_id</td><td>blocking_session_id</td><td>procedurename<
/td><td>status</td><td>wait_time</td><td>wait_type</td>' +
N'<td>wait_resource</td><td>transaction_id</td><td>lock_timeout</td></tr>' +
CAST((
SELECT
td = req.session_id,'',
td = blocking_session_id,'',
td = ib.event_info,'',
td = req.status ,'',
td = wait_time,'',
td = wait_type,'',
td = wait_resource,'',
td = transaction_id,'',
td = req.lock_timeout,''
from [sys].[dm_exec_requests] req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
INNER JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE [blocking_session_id] <> 0
and wait_time > 80000
order by wait_time desc
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ;
-- Send email
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Trendz_Email_Profile',
@body = @tableHTML,
@body_format = 'html',
@recipients = '[email protected]',
@subject = 'ALERT: (SP) Blocked Queries on EC2AMAZ-7VUVI7M';
-- Cleanup: Delete records older than 5 days
DELETE FROM dbo.PermanentBlockedQueries
WHERE LogDate < DATEADD(DAY, -5, GETDATE());
END
END
GO
