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 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

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