DB-유

SQL Server Recovery Models Explained: Simple, Full, Bulk-Logged

· mssql, backup, transaction-log

I have been paged more than once for a full log drive, and every time the cause was the same: a database in Full recovery that nobody was taking log backups for. The recovery model decides how the transaction log is managed and what restores are possible, and it quietly causes a lot of pain. Full without log backups fills the disk. Simple can't be restored to a point in time, no matter how good your full backups are.

Here is what each model does, how to check and change it, and how to set up log backups so the change doesn't bite you later.

Check what you have

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
ORDER BY name;

log_reuse_wait_desc is worth looking at while you are here. LOG_BACKUP means the log cannot be truncated until a log backup runs; NOTHING means the log is being reused normally.

The three models

Simple. The log is truncated automatically at every checkpoint. You cannot take log backups, so the only restore points are full and differential backups. Suitable for development, staging, and databases you can rebuild from another source. Not suitable for anything where losing hours of data matters.

Full. Every operation is fully logged and the log is only truncated when you back it up. This gives you point-in-time restore and is required for Always On Availability Groups, database mirroring and log shipping. The obligation is that you must schedule log backups, otherwise the log file grows forever.

Bulk-Logged. Behaves like Full, except that bulk operations (BULK INSERT, SELECT INTO, CREATE INDEX, bcp) are minimally logged. The log backup that covers a minimally logged operation contains the changed data extents, so it can be larger than expected, and you cannot restore to a point in time inside such a log backup, only to its end. I treat it as a temporary switch around a large load, not a permanent setting.

Changing it

ALTER DATABASE Sales SET RECOVERY FULL;

Or SIMPLE or BULK_LOGGED as needed. The change is immediate and does not require exclusive access.

One important detail when switching from Simple to Full: the log chain does not start until you take a full (or differential) backup after the switch. Until then the database behaves as if it were still in Simple mode, a state sometimes called pseudo-simple. So the correct sequence is:

ALTER DATABASE Sales SET RECOVERY FULL;
BACKUP DATABASE Sales TO DISK = N'D:\Backup\Sales_full.bak' WITH INIT, COMPRESSION, CHECKSUM;
BACKUP LOG Sales TO DISK = N'D:\Backup\Sales_log_01.trn' WITH INIT, COMPRESSION, CHECKSUM;

Log backups

With Full recovery, log backups are not optional. I start with an Agent job every 15 minutes and go from there. The T-SQL step:

DECLARE @file nvarchar(260) =
    N'D:\Backup\Sales_log_' + FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + N'.trn';
BACKUP LOG Sales TO DISK = @file WITH COMPRESSION, CHECKSUM;

The interval is your recovery point objective. If the business is fine losing 15 minutes of work, 15 minutes is fine. If not, run it every 5; log backups of a quiet database are tiny.

Bulk-Logged around a big load

ALTER DATABASE Sales SET RECOVERY BULK_LOGGED;
BULK INSERT dbo.Staging FROM 'D:\Import\big.csv' WITH (TABLOCK, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');
ALTER DATABASE Sales SET RECOVERY FULL;
BACKUP LOG Sales TO DISK = N'D:\Backup\Sales_log_after_load.trn' WITH COMPRESSION, CHECKSUM;

Take a log backup immediately after switching back so that the minimally logged interval is confined to a single log backup file.

Is the chain intact?

Look at msdb:

SELECT bs.database_name, bs.type, bs.backup_start_date, bs.first_lsn, bs.last_lsn
FROM msdb.dbo.backupset bs
WHERE bs.database_name = 'Sales'
ORDER BY bs.backup_start_date DESC;

Type D is full, I is differential, L is log. Each log backup's first_lsn should equal the previous one's last_lsn. A gap means the chain is broken and you need a new full backup.

Also confirm that the log is being reused:

DBCC SQLPERF(LOGSPACE);

If the "Log Space Used (%)" for a Full recovery database keeps climbing between log backups and never drops, something is holding the log.

Things that bit me

  • Msg 9002: The transaction log for database 'Sales' is full due to 'LOG_BACKUP'. Classic Full-recovery-without-log-backups. Take a log backup now, then set up the job from the log backups section above. Do not switch to Simple just to shrink the log unless you accept losing point-in-time restore.
  • Msg 4214: BACKUP LOG cannot be performed because there is no current database backup. You switched to Full but have not taken a full backup since. Run one.
  • Msg 4208: The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Somebody changed the model, or the maintenance job was pointed at a Simple database. Check sys.databases and fix whichever side is wrong.
  • Log backup after a Bulk-Logged operation is huge. Expected: the backup includes the data extents touched by minimally logged operations. This is also why point-in-time restore is not possible inside that backup.

Full for anything I'd be sorry to lose, Simple for anything I can rebuild, Bulk-Logged only as a short switch. And check the backup schedule actually matches the model. That mismatch is behind most of the log-full calls I get.