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

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

tech-coffee-sql-server2

Agent alerts can send you notification by email when an action (or) timeout occur between databases in your Availability Group environment. It allows you to be proactive when something not expected happens.

The following script will create what I consider “essential alerts” to monitor you AlwaysOn.

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 task to perform a required action.
I’d recommend creating a job to execute the Stored Procedure provided in the link below, to return the current “synchronism status” once the alert is fired.

https://danilocbraga.wordpress.com/2014/10/20/how-to-monitor-alwayson-primary-secondary-replicas/

Cheers!

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

I’ve developed a stored procedure to run on SQL Server Agent Job, to monitor the synchronism status from AlwaysOn Availability Groups.
In case of “NO HEALTHY” status, the SP will send you an email providing details of the database.

Pre-Requirements

– DatabaseMail Instaled

So, to set up the alert properly, I execute the stored procedure above in a job task (every 5 or 10 minutes) on the primary instance, passing ‘Primary’ as parameter.

EXEC [pr_monitor_alwayson] 'PRIMARY'

On the “expected” secondary replica, I pass ‘SECONDARY’ as parameter using the same stored procedure

EXEC [pr_monitor_alwayson] 'SECONDARY'

Once you need to force a failover, you should just change the parameters in both instances.

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 with more details about each event that occur in AlwaysOn Availability Groups.

Please, let me know if you have any questions.

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