SQL Server Recovery Model: Think Recovery, Not Disk Space

The alert arrives early in the morning: the disk holding a production database is full, and the application can no longer write. The culprit is a transaction log file that has grown to hundreds of gigabytes. An online search suggests an easy way out, and within minutes the database is switched to the SIMPLE recovery model, the log is shrunk, and the system is back online. Nobody notices that a much larger decision was just made in passing. With only a nightly backup, the business has quietly accepted that it could lose up to a full day of transactions, and no one outside the server room was asked.

Why does the transaction log fill the disk?

SQL Server records changes in the transaction log before they reach the data files. What happens to those log records next depends on the recovery model, a setting specific to each database. Under SIMPLE, the engine automatically reuses log space once it is no longer needed. Under FULL, the log keeps every change until a log backup is taken, because those records allow a restore to a precise moment. Its close variant, BULK_LOGGED, holds the log the same way but minimally logs most bulk operations. When a FULL database gets full backups but no log backups, nothing frees that space, and the file grows until it reaches its size limit or fills the disk. Recovery models have existed since SQL Server 2000, yet we regularly find databases whose recovery model nobody chose. Every database created on an instance inherits its setting from the model system database, so the configuration often reflects a default rather than a decision.

What does switching to SIMPLE actually cost?

When the log is only waiting for a backup, switching to SIMPLE does solve the disk problem, and that is exactly what makes it dangerous. Under SIMPLE, a database can only be restored to the end of its most recent backup, so every change made since then is unprotected. With one full backup a night, that can mean a day of orders, invoices, and production records to re-enter by hand, if they can be reconstructed at all. Restoring to the minute before a bad update is no longer possible. The switch also breaks the log backup chain, so no log backup can carry the database past that point until it returns to FULL and a new full or differential backup is taken. Picture a manufacturer whose order system fails that same afternoon: the restore brings back last night’s data, and the business discovers a recovery promise it made without knowing it.

How should we choose the right recovery model?

The right recovery model is the technical answer to two business questions. How much data can we afford to lose, which is the recovery point objective, or RPO? How quickly must we be running again, which is the recovery time objective, or RTO? Those answers belong to the people who own the business process, not only to whoever manages the server. A database that can tolerate only a few minutes of loss runs under FULL, with log backups at least that frequent. A reporting copy rebuilt nightly from its sources can safely run under SIMPLE, since losing it only costs a reload. The answer is rarely the same across an environment, so a healthy one documents targets for each database, matches its recovery model and backups to them, and tests restores to prove they can be met. Disk space then becomes a sizing question planned in advance, not the trigger for a recovery decision.

How do we bring an existing environment back in line?

Getting this right calls for a deliberate sequence rather than a large project. First, we list every database with its recovery model, from sys.databases, and the dates of its last full and log backups, from the backup history SQL Server keeps in msdb. Second, we agree with the business owners on an RPO and RTO for each database, starting with revenue-generating systems. Third, we schedule log backups where FULL is required and use SIMPLE only where the agreed tolerance allows it. When a database moves from SIMPLE to FULL, we take a full or differential backup right away, because the change only takes effect after that first data backup. Fourth, once log backups run reliably, we resize any oversized log file once and leave it alone, since repeated shrinking only makes the file grow back. Finally, we test a restore against each target, because an untested recovery objective is only a hope.

This is less about a configuration setting than about when the recovery conversation happens. Switching to SIMPLE after the disk fills up is a reactive choice, made under pressure by whoever is on call. The stronger position is to set recovery targets before anything breaks and to treat log growth as a routine signal rather than an emergency. When the recovery model reflects a decision the business actually made, a full disk becomes a minor maintenance event instead of a hidden gamble, and everyone knows what a restore will bring back. That certainty is worth far more than the disk space it costs.

FAQ

Why does a SQL Server transaction log keep growing?
A common cause is a FULL recovery database without regular log backups. Long-running transactions, replication, or a lagging availability group replica can also block log reuse. SQL Server shows the reason in the log_reuse_wait_desc column of sys.databases.

What is the BULK_LOGGED recovery model used for?
BULK_LOGGED is a variant of FULL that minimally logs most bulk operations, such as large data loads. It still requires log backups, and a log backup containing bulk operations can only be restored to its end, not to a moment within it.

Should every SQL Server database use the FULL recovery model?
No, because the right choice depends on how much data each database can afford to lose. Always On availability groups require FULL and log shipping requires FULL or BULK_LOGGED, while a staging database rebuilt from its sources can often run under SIMPLE.

Not sure where your environment stands? Book a free 30-minute conversation with our team.