Sample Visio diagram : DOWNLOAD
Friday 7 May 2021
Thursday 6 May 2021
Blocking Monitoring Alert
SET NOCOUNT ON
DECLARE @xml nvarchar(max)
SELECT @xml = Cast((SELECT @@SERVICENAME AS 'td','',b.session_id AS 'td',
'',
(b.wait_duration_ms/1000)/60 AS 'td',
'',
b.wait_type AS 'td',
'',
b.blocking_session_id AS 'td',
'',
t.text AS 'td'
FROM sys.dm_os_waiting_tasks b inner join sys.dm_exec_requests r on r.session_id= b.session_id
OUTER APPLY
sys.dm_exec_sql_text(sql_
WHERE b.blocking_session_id <> 0 and b.wait_duration_ms>180
FOR xml path('tr'), elements) AS NVARCHAR(max))
Declare @body nvarchar(max)
SET @body =
'<html>
<head>
<style>
table, th, td
{
border: 1px solid black;
border-collapse: collapse;
text-align: center;
}
</style>
</head>
<body>
<H2>
Blocking queries
</H2>
<table>
<tr>
<th>Instance Name</th><th> Blocked Session ID </th> <th> Wating in minutes </th> <th> Wait type</th>
<th> Blocking Session ID</th><th>Query waiting to execute</th>
</tr>'
SET @body = @body + @xml + '
</table>
</body>
</html>'
if(@xml is null)
BEGIN
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'SQL Server Agent Alerts',
@body = @body,
@body_format ='html',
@recipients = 'harikrishna.m@canberra.edu.au
@subject = 'Blocking More Than 3 Min';
END
SET NOCOUNT OFF
Wednesday 5 May 2021
SQL Server Always On Monitoring Alerts
Set Nocount On;
Declare @alertName sysname
, @thisErrorNumber varchar(6)
, @sqlCommand nvarchar(max) = ''
, @operatorName sysname = 'Database Administration';
Declare @errorNumbers Table (ErrorNumber varchar(6), AlertName varchar(50));
Insert Into @errorNumbers
Values ('1480' , 'AG Role Change (failover)')
, ('976' , 'Database Not Accessible')
, ('983' , 'Database Role Resolving')
, ('3402' , 'Database Restoring')
, ('19406', 'AG Replica Changed States')
, ('35206', 'Connection Timeout')
, ('35250', 'Connection to Primary Inactive')
, ('35264', 'Data Movement Suspended')
, ('35273', 'Database Inaccessible')
, ('35274', 'Database Recovery Pending')
, ('35275', 'Database in Suspect State')
, ('35276', 'Database Out of Sync')
, ('41091', 'Replica Going Offline')
, ('41131', 'Failed to Bring AG Online')
, ('41142', 'Replica Cannot Become Primary')
, ('41406', 'AG Not Ready for Auto Failover')
, ('41414', 'Secondary Not Connected');
Declare cur_ForEachErrorNumber Cursor Local fast_forward
For
Select *
From @errorNumbers;
Open cur_ForEachErrorNumber;
Fetch Next From cur_ForEachErrorNumber Into @thisErrorNumber, @alertName;
While @@fetch_status = 0
Begin
If Not Exists(Select *
From msdb.dbo.sysalerts s
Where s.message_id = @thisErrorNumber)
Begin
Execute msdb.dbo.sp_add_alert
@name = @alertName
, @message_id = @thisErrorNumber
, @severity = 0
, @enabled = 1
, @delay_between_responses = 0
, @include_event_description_in = 1
, @job_id = N'00000000-0000-0000-0000-000000000000';
Execute msdb.dbo.sp_add_notification
@alert_name = @alertName
, @operator_name = @operatorName
, @notification_method = 1;
Raiserror('Alert ''%s'' for error number %s created.', -1, -1, @alertName, @thisErrorNumber) With nowait;
End
Fetch Next From cur_ForEachErrorNumber Into @thisErrorNumber, @alertName;
End
--==== Close/Deallocate cursor
Close cur_ForEachErrorNumber;
Deallocate cur_ForEachErrorNumber;
Subscribe to:
Posts (Atom)