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

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:

1-- Example of problematic scenario
2-- Assume a database in Full Recovery Model without recent transaction log backups
3ALTER DATABASE MyDb SET RECOVERY FULL; 
4-- 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.

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:

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

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

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

Maintenance Rules, Bugs

Recover from a full transaction log in a SQL Server database

Other Resources