Skip to main content
How can we help?
< All Topics
Print

Log backups older than expected

Alert is disabled by default but can be enabled in dbo.alerts by changing enabled from 0 to 1.

Alert will be thrown if the last log backup is older than expected. The alert can be configured by editing the statement in the sql column in dbo.alerts.

The values for when the alert should trigger can be configured by the following values:

declare @logBackupIntervalInMinutes int = 60
declare @maxLogBackupTimeInMinutes int = 30

@logBackupIntervalInMinutes defines how many minutes there are between the backups.

@maxLogBackupTimeInMinutes defines the maximum time it takes for the backup to complete.

To query the Performance Store database for log backups that are older than expected, use:

declare @logBackupIntervalInMinutes int = 60
declare @maxLogBackupTimeInMinutes int = 30

declare @localManagementDatabase nvarchar(128)

select @localManagementDatabase = c.value
from dbo.config c
where c.name = 'LocalManagementDatabase'

declare @dummyDatabaseName nvarchar(128)

select @dummyDatabaseName = c.value
from dbo.config c
where c.name = 'DummyDatabaseName'

select s1.server_name, s1.database_name, s1.last_log_backup_date
from
(
	select d.server_name, d.database_name, d.last_log_backup_date, d.last_failover_date, d.availability_group_name, d.create_date
	from dbo.v_databases d
	inner join
	(
		select d1.group_database_id, max(d1.last_log_backup_date) last_log_backup_date, max(d1.last_failover_date) last_failover_date
		from dbo.v_databases d1
		where d1.group_database_id is not null
		group by d1.group_database_id
	) s on s.group_database_id = d.group_database_id and s.last_log_backup_date = d.last_log_backup_date and d.role_desc = 'PRIMARY'

	union all

	select d.server_name, d.database_name, d.last_log_backup_date, d.last_failover_date, d.availability_group_name, d.create_date
	from dbo.v_databases d
	where d.group_database_id is null
	and d.state_desc = 'ONLINE'
	and d.recovery_model_desc != 'SIMPLE'
) s1
inner join dbo.v_servers s2 on s2.server_name = s1.server_name
where s1.last_log_backup_date < dateadd(minute, -(@logBackupIntervalInMinutes + @maxLogBackupTimeInMinutes), getdate())
and s1.create_date < dateadd(minute, -(@logBackupIntervalInMinutes + @maxLogBackupTimeInMinutes), getdate())
and s2.sample_time > dateadd(minute, -(@logBackupIntervalInMinutes + @maxLogBackupTimeInMinutes), getutcdate())
and s1.last_log_backup_date > s1.create_date
and (s1.availability_group_name is null or (s1.last_failover_date is null or s1.last_failover_date < dateadd(minute, -(@logBackupIntervalInMinutes + @maxLogBackupTimeInMinutes), getutcdate())))
and s1.database_name not in (@localManagementDatabase, N'tempdb', N'model', N'master', N'distribution', N'msdb', @dummyDatabaseName)
and s2.is_production = 1

See also: Full backups older than expected and Diff backups older than expected.

To exclude specific servers from this alert, see how to use the dbo.team_alert_exclude table in Alerting.

Table of Contents