Blocking is common in any RDBMS product, but it is important to get a notification if it is unusual. Steps to set up an automated email notification for any blocking greater than 30 min are:-
Step 1: Configure Database Mail
You can go here to setup DatabaseMail if you don't have one alreadyStep 2: Create Stored Procedure in the DBA utility database or any database using the below script. The script makes use of the SQL Server DatabaseMail & SQL Server Agent feature to send Blocking Notification either to the Database Team or Application Team if it is greater than 30 minutes. You can adjust the duration based on your business requirement.
USE DBA_DB --Change Database Name GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[BlockingReport] AS IF object_id('tempdb..#temp_requests') IS NOT NULL DROP TABLE #Password SELECT @@servername AS [ServerName] ,[Blocker SPID] = B.[SPID] ,[Blocker Last Batch] = B.[Last_Batch] ,[Blocker Open Tran] = B.[Open_Tran] , --[Blocked For] = convert(varchar,(V.waittime/(1000))/(60*60)) + ' Hours' + convert(varchar,(V.waittime/(1000))/60) + ' Minutes ' + convert(varchar,(V.waittime/(1000))%60) + ' Seconds' , [Blocked For_hh:mm:ss] = convert(VARCHAR, dateadd(ms, V.waittime, 0), 114) ,[Blocker Login Name] = B.[LogiName] ,[Blocker Host Name] = B.[HostName] ,[Blocker Program Name] = B.[Program_Name] ,[Blocker status] = B.[Status] ,[Blocker Db Name] = DB_NAME(B.[DBID]) ,[Blocker Command Text] = BlockerText.[text] ,[Victim SPID] = V.[SPID] ,[Victim Last Batch] = V.[Last_Batch] ,[Victim Open Tran] = V.[Open_Tran] ,[Victim Wait Time_hh:mm:ss] = convert(VARCHAR, dateadd(ms, V.waittime, 0), 114) ,[Victim Login Name] = V.[LogiName] ,[Victim Host Name] = V.[HostName] ,[Victim Program Name] = V.[Program_Name] ,[Victim status] = V.[Status] ,[Victim Db Name] = DB_NAME(V.[DBID]) ,[Victim Command Text] = VictimText.[text] INTO #temp_requests FROM [master].[dbo].[sysprocesses] B(NOLOCK) JOIN [master].[dbo].[sysprocesses] V(NOLOCK) ON V.Blocked = B.SPID OUTER APPLY [sys].[dm_exec_sql_text](B.sql_handle) BlockerText OUTER APPLY [sys].[dm_exec_sql_text](V.sql_handle) VictimText WHERE V.waittime > 1000 * 60 * 30 --30 Min AND V.Blocked != 0 AND V.Blocked != V.SPID DECLARE @count INT SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN BEGIN -- Email Querry-- DECLARE @Body VARCHAR(max) DECLARE @TableHead VARCHAR(max) DECLARE @TableTail VARCHAR(max) DECLARE @mailitem_id AS INT DECLARE @statusMsg AS VARCHAR(max) DECLARE @Error AS VARCHAR(max) DECLARE @Note AS VARCHAR(max) SET NOCOUNT ON; SET @mailitem_id = NULL SET @statusMsg = NULL SET @Error = NULL SET @Note = NULL SET @TableTail = '</table> <br/>Please review the Blocking Queries & take necessary action<br/> <br/>Thanks<br/>DBA Team</body></html>'; --HTML layout-- SET @TableHead = '<html><head>' + 'Hello,<br/> <br/> <br/> Please find the blocking details on SQL server : <br/> <br/> ' + '<style>' + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' + '</style>' + '</head>' + '<body><table cellpadding=0 cellspacing=0 border=0>' + '<tr bgcolor=#F6AC5D>' + '<td align=center><b>Server Name </b></td>' + '<td align=center><b>Blocker SPID </b></td>' + '<td align=center><b>Blocker Last Batch</b></td>' + '<td align=center><b> Blocker Open Tran </b></td>' + '<td align=center><b>Blocked For_hh:mm:ss </b></td>' + '<td align=center><b>Blocker Login Name </b></td>' + '<td align=center><b> Blocker Host Name</b></td>' + '<td align=center><b>Blocker Program Name </b></td>' + '<td align=center><b>Blocker status</b></td>' + '<td align=center><b>Blocker Db Name</b></td>' + '<td align=center><b> Blocker Command Text</b></td>' + '<td align=center><b> Victim SPID </b></td>' + '<td align=center><b>Victim Last Batch</b></td>' + '<td align=center><b> Victim Open Tran </b></td>' + '<td align=center><b> Victim Wait Time_hh:mm:ss </b></td>' + '<td align=center><b> Victim Login Name </b></td>' + '<td align=center><b> Victim Host Name</b></td>' + '<td align=center><b> Victim Program Name </b></td>' + '<td align=center><b> Victim status</b></td>' + '<td align=center><b> Victim Db Name</b></td>' + '<td align=center><b> Victim Command Text</b></td></tr>'; SELECT @Body = ( SELECT [ServerName] AS [TD] ,[Blocker SPID] AS [TD] ,[Blocker Last Batch] AS [TD] ,[Blocker Open Tran] AS [TD] ,[Blocked For_hh:mm:ss] AS [TD] ,[Blocker Login Name] AS [TD] ,[Blocker Host Name] AS [TD] ,[Blocker Program Name] AS [TD] ,[Blocker status] AS [TD] ,[Blocker Db Name] AS [TD] ,[Blocker Command Text] AS [TD] ,[Victim SPID] AS [TD] ,[Victim Last Batch] AS [TD] ,[Victim Open Tran] AS [TD] ,[Victim Wait Time_hh:mm:ss] AS [TD] ,[Victim Login Name] AS [TD] ,[Victim Host Name] AS [TD] ,[Victim Program Name] AS [TD] ,[Victim status] AS [TD] ,[Victim Db Name] AS [TD] ,[Victim Command Text] AS [TD] FROM #temp_requests FOR XML raw('tr') ,Elements ) SET @Body = Replace(@Body, '_x0020_', space(1)) SET @Body = Replace(@Body, '_x003D_', '=') SET @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>') SET @Body = Replace(@Body, '<TRRow>0</TRRow>', '') SET @Body = @TableHead + @Body + @TableTail EXEC msdb.dbo.sp_send_dbmail @profile_name = '' ,-- replace with your SQL Database Mail Profile @body = @body ,@body_format = 'HTML' ,@recipients = '' ,-- replace with your email address @subject = 'Blocking Detected'; --EXEC msdb.dbo.sysmail_help_profile_sp; DROP TABLE #temp_requests END END
Step 3: Schedule a SQL Server Agent job to execute the Stored Procedure created(in step 2) to run every 15 minutes
Done!! I hope this helps 😊
No comments:
Post a Comment