SA0099 : The database is using Full Recovery Model, but its last transaction log backup is too old

Introduction

The problem occurs when a database using the Full Recovery Model lacks recent transaction log backups.

Description

When a SQL Server database operates in Full Recovery Model, it maintains a complete transaction log, which is crucial for point-in-time recovery. Without regular backups of these logs, several issues can arise.

For example:

-- Example of problematic scenario
-- Assume a database in Full Recovery Model without recent transaction log backups
ALTER DATABASE MyDb SET RECOVERY FULL;
-- No backup command executed for the transaction log

In this scenario, the lack of regular transaction log backups can lead to excessive growth of the log file, consuming unnecessary disk space and hindering database recovery.

The transaction log file can become excessively large, leading to potential storage issues.
Without recent log backups, it becomes impossible to perform point-in-time recovery, risking data loss in the event of failure.

How to fix

This section provides steps to fix issues related to the lack of recent transaction log backups in databases using the Full Recovery Model.

Follow these steps to address the issue:

1.Ensure the database is set to use the Full Recovery Model by executing the following SQL command: ALTER DATABASE MyDb SET RECOVERY FULL;

2.Create a transaction log backup to prevent excessive log file growth and enable point-in-time recovery. Execute a backup command such as:

For example:

-- Create a transaction log backup for the database
BACKUP LOG MyDb TO DISK = 'C:\backup\MyDb_log.bak';

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Name	Description	Default Value
ExpirationDays	Number of days the after which the transaction log backup are considered outdated.	1

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Effort To Fix

1 hour per issue.

Additional Information

Recover from a full transaction log in a SQL Server database

Analysis Rules