Database Log Backup Status

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

This monitor checks the status of the database log backup as reported by Microsoft SQL Server. Note that this 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 database log backup.

Note that this monitor does not track log status in cases when the database is part of an Availability group.

Causes

Database log has not been backed up for too long.

Resolutions

You should perform a database log backup 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.

Additional

Database Log Backup Status monitor only considers the backup of transaction log. Full and differential database backups don’t contain much SQL Server transaction log information, only the transactions necessary to recover the database into a consistent state.

The monitor is disabled by default and if you want to enable the monitoring scenario for your environment, it is recommended to keep the monitor disabled for all servers which are not used for storing the database log backup.

External

Best Practice recommendations for SQL Server Database Backups

Overridable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

Normal

Alert Severity

Defines Alert Severity.

Error

Backup Period (minutes)

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

90

Enabled

Enables or disables the workflow.

No

Generates Alerts

Defines whether the workflow generates an Alert.

Yes

Interval (seconds)

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

5400

Synchronization Time

The synchronization time specified by using a 24-hour format. May 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.Database
Parent MonitorSystem.Health.AvailabilityState
CategoryAvailabilityHealth
EnabledFalse
Alert GenerateTrue
Alert SeverityError
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.Windows.MonitorType.Database.DBLogBackupStatus
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL on Windows: Database log backup should be performed
Database logs "{2}" on SQL Server instance "{1}", computer "{0}" have not been backed up for {3} minutes.
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Windows.Monitor.Database.DBLogBackupStatus" Accessibility="Public" Enabled="false" Target="SqlDiscW!Microsoft.SQLServer.Windows.Database" ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Windows.MonitorType.Database.DBLogBackupStatus" ConfirmDelivery="false">
<Category>AvailabilityHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.Windows.Monitor.Database.DBLogBackupStatus.AlertMessage">
<AlertOnState>Error</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>Error</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</AlertParameter1>
<AlertParameter2>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</AlertParameter2>
<AlertParameter3>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.Database"]/DatabaseName$</AlertParameter3>
<AlertParameter4>$Data/Context/Property[@Name='MinutesSinceBackup']$</AlertParameter4>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Good" MonitorTypeStateID="Good" HealthState="Success"/>
<OperationalState ID="Bad" MonitorTypeStateID="Bad" HealthState="Error"/>
</OperationalStates>
<Configuration>
<MachineName>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>
<NetbiosComputerName>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>
<InstanceName>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>
<ConnectionString>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>
<InstanceVersion>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>
<InstanceEdition>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>
<MonitoringType>$Target/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>
<DatabaseName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.Database"]/DatabaseName$</DatabaseName>
<Threshold>90</Threshold>
<IntervalSeconds>5400</IntervalSeconds>
<SyncTime/>
<SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
<TimeoutSeconds>300</TimeoutSeconds>
</Configuration>
</UnitMonitor>