Availability Database Log Backup Status

Microsoft.SQLServer.Windows.Monitor.AvailabilityDatabaseHealth.DBLogBackupStatus (UnitMonitor)

This monitor checks the status of the database log backup as reported by Microsoft SQL Server, it does not apply any logic regarding the replicas preferred for the backup. The monitor is disabled by default. Use overrides to enable it when necessary.

Knowledge Base article:

Summary

This monitor checks the existence and age of database log backup as reported by Microsoft SQL Server. This is done by running a query against the master database of the SQL instance and returning the age of the log backup. The monitor is disabled by default. Use overrides to enable it when necessary.

Note that due to the Availability Group database log backup specifics, the alerting for this unit monitor is disabled by default and it is not recommended to enable it. The actual health state of the availability database log backup shows the "Availability Database Log Backup Status (rollup)", which has the 'Best state of any member' health rollup policy option.

Causes

Database log has not been backed up for too long.

Resolutions

You should perform a backup of one of the availability database replicas to resolve the issue.

To eliminate this issue in future it is recommended to schedule an automated backup according to the needs of the application and business requirements.

External

Best Practice recommendations for SQL Server Database Log Backups

Overridable Parameters

Name

Description

Default Value

Backup Period (minutes)

The target backup frequency in minutes. Should be set according to your Recovery Point Objective (RPO).

90

Number of samples

Indicates how many times a measured value should breach a threshold before the state is changed.

2

Enabled

Enables or disables the workflow.

No

Generates Alerts

Defines whether the workflow generates an Alert.

No

Interval (seconds)

The recurring interval of time in seconds in which to run the workflow.

3600

Synchronization Time

The synchronization time is specified by using a 24-hour format. Can be omitted.

 

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

300

Timeout for query execution (seconds)

The workflow will fail and register an event, if the query execution takes longer than the specified period.

60

Timeout for database connection (seconds)

The workflow will fail and register an event, if it cannot access the database during the specified period.

15

Element properties:

TargetMicrosoft.SQLServer.Windows.AvailabilityDatabaseHealth
Parent MonitorSystem.Health.AvailabilityState
CategoryAvailabilityHealth
EnabledFalse
Alert GenerateFalse
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.Windows.MonitorType.AvailabilityDatabaseHealth.DBLogBackupStatus
RemotableTrue
AccessibilityPublic
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Windows.Monitor.AvailabilityDatabaseHealth.DBLogBackupStatus" Accessibility="Public" Enabled="false" Target="SqlDiscW!Microsoft.SQLServer.Windows.AvailabilityDatabaseHealth" ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Windows.MonitorType.AvailabilityDatabaseHealth.DBLogBackupStatus" ConfirmDelivery="false">
<Category>AvailabilityHealth</Category>
<OperationalStates>
<OperationalState ID="Good" MonitorTypeStateID="Good" HealthState="Success"/>
<OperationalState ID="Bad" MonitorTypeStateID="Bad" HealthState="Error"/>
</OperationalStates>
<Configuration>
<MachineName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>
<NetbiosComputerName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>
<InstanceName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>
<DatabaseName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.AvailabilityDatabaseHealth"]/DatabaseName$</DatabaseName>
<ConnectionString>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>
<InstanceVersion>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>
<InstanceEdition>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>
<MonitoringType>$Target/Host/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>
<Threshold>90</Threshold>
<NumSamples>2</NumSamples>
<SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
<TimeoutSeconds>300</TimeoutSeconds>
<IntervalSeconds>3600</IntervalSeconds>
<SyncTime/>
</Configuration>
</UnitMonitor>