Alerts

Alert – Databases do not have Transaction log backups

The SP below sends alert when there are no transaction log backups in the last 90 minutes.

CREATE PROCEDURE [dbo].[usp_NoTlogBackup] (@Length INT=90) -- 90 Minutes
AS
DECLARE @BadDatabases VARCHAR(8000)
DECLARE @Body VARCHAR(8000)

SELECT @BadDatabases = STUFF(
(select ', ' + cast(a.database_name as varchar(100))
 from 
 (
 select [name] as database_name
 from master.dbo.sysdatabases
 where databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED') 
 and databasepropertyex([name], 'isinstandby') = 0
 and databasepropertyex([name], 'status') = 'online'
 and databasepropertyex([name], 'updateability') = 'read_write'
 and [name] not in ('model','tempdb')
 ) a
 left join msdb.dbo.backupset b
 on a.database_name = b.database_name 
 and b.type='L' 
 and datediff(hour,b.backup_finish_date,getdate()) < @Length
 where b.database_name is null
FOR XML PATH ('')
),1,2,'')

IF (@BadDatabases IS NOT NULL)
BEGIN

declare @ServerT varchar(100)
select @ServerT = 'Alert Backup - '+@@SERVERNAME

SET @Body = 'On ' + @@SERVERNAME + ' The following Databases do not have Transaction log backups: ' + @BadDatabases
PRINT @Body

 EXEC msdb . dbo. sp_send_dbmail        
      @recipients=N'mail@mail.com;' ,
      @body = @Body ,         
      @subject = @ServerT,         
      @profile_name ='profile_name',         
      @body_format = 'HTML'

END
Advertisements

How to monitor Timeout and Changes Roles from Availability Groups with Alerts

tech-coffee-sql-server2

Agent alert can send alerts by email when occur some action/timeout between databases in your Availability Group Environment. It allows you to become proactive.

The following script shows how to create these alerts.

EXEC msdb .dbo. sp_add_operator @name =N'AlertTeam',
               @enabled=1 ,
               @weekday_pager_start_time=90000 ,
               @weekday_pager_end_time=180000 ,
               @saturday_pager_start_time=90000 ,
               @saturday_pager_end_time=180000 ,
               @sunday_pager_start_time=90000 ,
               @sunday_pager_end_time=180000 ,
               @pager_days=0 ,
               @email_address=N'email@email.com.br' ,
               @category_name=N'[Uncategorized]'
GO

-- 1480 - AG Role Change (failover)
EXEC msdb .dbo. sp_add_alert
        @name = N'AG Role Change',
        @message_id = 1480,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb .dbo. sp_add_notification
        @alert_name = N'AG Role Change',
        @operator_name = N'AlertTeam',
        @notification_method = 1;
GO

-- 35264 - AG Data Movement - Resumed
EXEC msdb .dbo. sp_add_alert
        @name = N'AG Data Movement - Suspended',
        @message_id = 35264,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb .dbo. sp_add_notification
        @alert_name = N'AG Data Movement - Suspended',
        @operator_name = N'AlertTeam',
        @notification_method = 1;
GO

-- 35265 - AG Data Movement - Resumed
EXEC msdb .dbo. sp_add_alert
        @name = N'AG Data Movement - Resumed',
        @message_id = 35265,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb .dbo. sp_add_notification
        @alert_name = N'AG Data Movement - Resumed',
        @operator_name = N'AlertTeam',
        @notification_method = 1;
GO

-- 35206 - AG Timeout to Secondary Replica
EXEC msdb .dbo . sp_add_alert
        @name = N'AG Timeout to Secondary Replica',
        @message_id = 35206,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb .dbo . sp_add_notification
        @alert_name = N'AG Timeout to Secondary Replica',
        @operator_name = N'AlertTeam',
        @notification_method = 1;
GO
-- 35202 - AG Timeout to Secondary Replica
EXEC msdb .dbo . sp_add_alert
        @name = N'AG Connection has been successfully established',
        @message_id = 35202,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb .dbo . sp_add_notification
        @alert_name = N'AG Connection has been successfully established',
        @operator_name = N'AlertTeam',
        @notification_method = 1;

You can also configure to execute another SQL Agent job to perform your required action.
I recommend you to execute a Job with the Stored Procedure inside the link below to show how is the synchronism status.
https://danilocbraga.wordpress.com/2014/10/20/how-to-monitor-alwayson-primary-secondary-replicas/

I hope this post can help

How to Monitor AlwaysON – Primary / Secondary Replicas on SQL Server

Last month I developed a procedure to run in a SQL Server Agent Job, to monitor the synchronism status of the AlwaysOn Availability Groups.
In case of “NO HEALTHY” status, the SP will send an email providing details of every databases.

Pre-Requirements

– DatabaseMail Instaled

Ex:
EXEC [pr_monitor_alwayson] ‘PRIMARY’ –On Primary replica
EXEC [pr_monitor_alwayson] ‘SECONDARY’ –On Secondary replica

Related Links:

Monitor Availability Groups (Transact-SQL)

In the next post, I’m going to provide scripts of alerts that I usually configure to complement details about each event that occur in AlwaysOn Availability Groups.

I hope this helps you. If you have any doubt, please let me know.

Easy script to notify failed jobs [SQL Server]

The script provided below is a easy way to add notification for all failed jobs.

Requirements:
1- DatabaseMail Enabled
2- Enable DatabaseMail on the SQL Server Agent ; properties ; Alert System

USE [msdb]
GO
--create a N'DBA_Operator'
EXEC msdb.dbo.sp_add_operator @name=N'DBA_Operator',          
@enabled=1,
@email_address=N'DBAs@yourdomain.com'
GO
--add notifications for failure to all jobs
DECLARE @QuotedIdentifier char(1); SET @QuotedIdentifier = '' -- use '''' for single quote
DECLARE @ListDelimeter char(1); SET @ListDelimeter = ';'
DECLARE @CSVlist varchar(max) --use varchar(8000) for SQL Server 2000

--no event log, email on failure
SELECT     @CSVlist = COALESCE(@CSVlist + @ListDelimeter, '') + @QuotedIdentifier +
'
EXEC msdb.dbo.sp_update_job @job_id=N'''
+ convert(varchar(max),[job_id]) +
''',
          @notify_level_eventlog=0,
          @notify_level_email=2,
          @notify_email_operator_name=N''DBA_Operator'''
+ @QuotedIdentifier
from msdb.dbo.sysjobs