Database Log backup

Job details

Name:	Database Log backup
Platform:	Sqlserver
Category:	Availability
Description:	This procedure checks the database transaction log backups from the msdb.dbo.backupset table (excluding system databases: master, model and msdb).
Long description:	This procedure checks the database transaction log backups from the msdb.dbo.backupset table (excluding system databases: master, model and msdb).
Version:	2.3
Default schedule:	20 0,2,4,6,8,10,12,14,16,18,20,22 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2012′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
ignore databases	model	The list of databases to be ignored (separated by comma).
warning threshold	4	A warning is triggered if the number of hours since the last transaction log has been backed up is greater than this parameter.
return status when missing backup	2	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when missing transaction log backup of a database.
return status when old backup	2	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the last backup date of the transaction log is older than the “backup date threshold” parameter value.
exclude databases if new	24	If the database has been created within the last 24 hours (default value), no warning/alarm will be triggered.
exclude databases in Always On availability groups	YES	If set to YES the alert will exclude all databases in Always On availability groups.
exclude log shipping databases	YES	If set to YES the alert will exclude all databases in a log shipping configuration (databases in msdb.dbo.log_shipping_secondary_databases table).
exclude standby databases	YES	If set to YES the alert will exclude all databases in state online as read-only, with restore log allowed (IsInStandBy property set to TRUE).

Job Summary

Purpose: The purpose of this job is to monitor database log backups for Microsoft SQL Server, ensuring all transaction logs are backed up appropriately to prevent data loss and ensure database integrity.
Why: This job is critical as it verifies the recency of log backups, which is crucial for database recovery and minimizes potential data loss in the event of failures. Upon detecting missed backups or excessive backup intervals, it raises alerts to prevent serious complications that can arise from dated or absent log backups.
Manual Checking: Administrators can manually check the backup status using the SQL queries within the script or by querying the msdb.dbo.backupset directly:

SELECT database_name, MAX(backup_finish_date) AS last_backup FROM msdb.dbo.backupset WHERE type = 'L' GROUP BY database_name;

Job Details

Core Functionality

The task uses a stored procedure “dbw_backup_log_proc” to examine the backup status of transaction logs by querying the “msdb.dbo.backupset” table.
It checks all databases except system databases (master, model, and msdb), ignoring databases based on various conditions set by parameters such as the presence in Always On availability groups, log shipping configurations, or standby databases.

Implementation Specifics

The procedure initiates checks to determine transaction log backup recency, raising alerts if backups are not within set thresholds (defined by “warning threshold” parameter).
Jobs pinpoint missing backups and databases whose last backups exceed a certain age, listing them with specifics on the last backup time to provide clear diagnostics.
It incorporates conditional logic to exclude newly created databases from checks and provides exceptions for special database configurations to minimize false alerts.

Scheduled Checks

The monitoring job is executed based on a predefined schedule: every two hours throughout the day.
Such frequent checks enable timely detection of issues, allowing for quick resolutions that maintain the high availability and resilience of the database system.

Dependencies and Adjustments

Depends on an internal table “dbw_ignore_log_db” for maintaining lists of databases to ignore during checks.
Incorporates automatic clean-up mechanisms that ensure only relevant data is maintained during each run, avoiding accumulation of stale or irrelevant data.

Changes and Upgrades

Routine updates to the procedure ensure continuous alignment with operational requirements and changes in database configurations.
Version control is meticulously maintained, with clear documentation on what changes with each new version to ensure transparency and trackability.

Conclusion

This dbWatch monitoring job plays an essential role in operational resilience by ensuring all critical transaction log data is backed up. It provides necessary alerts that prompt maintenance actions, helping to avert potential crises by maintaining up-to-date and accessible database backups.