SQL Server log backups missing: the full-recovery log that grows forever

The application starts throwing write errors. The database is online, reads work, but every INSERT, UPDATE, and DELETE fails with error 9002: the transaction log is full. The log volume is at zero free space, or the log file has auto-grown to many times the size of the data files. When you ask when the last log backup ran, nobody knows.

This is almost always the same root cause: the database is in FULL or BULK_LOGGED recovery, and log backups have stopped running. A full database backup does not truncate the log. Only BACKUP LOG does. If log backups are missing, disabled, or silently failing, the log accumulates every transaction forever until the file hits its max size or the disk fills.

The engine tells you exactly why the log cannot be truncated, and the fix is usually fast once you read the right signal. This guide covers distinguishing a dead backup job from a broken log chain, verifying what actually happened in msdb, and recovering without making things worse.

What this means

In FULL or BULK_LOGGED recovery, SQL Server keeps every log record so you can restore to any point in time by replaying an unbroken sequence of log backups: a full backup, then every log backup after it, in LSN order. That sequence is the log chain. Log space is only marked reusable (truncated) after a log backup has captured it. No log backup, no truncation, unlimited growth.

Two distinct failure modes produce the same symptom:

  • Log backups stopped running. The SQL Agent job was disabled, deleted, misscheduled, or is failing. Or the job “succeeds” but writes to a bad target: a full backup volume, a deleted network share, or NUL. The msdb history is the authority here, not the job status.
  • The log chain is broken. After a restore, a detach/attach, or a recovery model change, the previous chain no longer applies. A database switched from SIMPLE to FULL recovery needs a fresh full backup before any log backup will work; until then, log backups either fail or the database behaves as if it were still in SIMPLE (“pseudo-simple”). After a restore, a new full backup restarts the chain.

Either way, the database keeps writing log records it cannot reuse, and the file grows until writes stop.

Common causes

CauseWhat it looks likeFirst thing to check
Log backup job disabled, deleted, or failinglog_reuse_wait_desc = LOG_BACKUP; last type ‘L’ row in msdb.dbo.backupset is hours or days oldLast log backup per database in msdb.dbo.backupset
Job “succeeds” but writes to a bad targetJob history green, but backup files missing, zero-length, or on a full/deleted share; backups recorded to NULphysical_device_name in msdb.dbo.backupmediafamily; confirm the file exists and has size
Recovery model switched SIMPLE to FULL, no new full backupLog backups fail or log grows anyway; chain never restartedrecovery_model_desc in sys.databases plus last full backup (type ‘D’) in backupset
Restore or detach/attach broke the chainLog backups error about the chain; no common basebackupset history around the restore time
Someone ran BACKUP LOG to NUL to “clear” the logPoint-in-time recovery silently destroyed; backupset shows physical_device_name = ‘NUL’backupmediafamily for NUL devices
Long-running transaction, replication, or AG lag (not a backup problem at all)log_reuse_wait_desc = ACTIVE_TRANSACTION, REPLICATION, or AVAILABILITY_REPLICAlog_reuse_wait_desc in sys.databases before touching backups
Third-party VSS snapshot backups assumed to truncateSnapshot backups may not truncate the log; log keeps growing despite “backups”is_snapshot / is_copy_only flags in backupset for recent backups

One thing that does not break the chain: copy-only backups. BACKUP DATABASE … WITH COPY_ONLY and BACKUP LOG … WITH COPY_ONLY are safe for ad-hoc copies and leave the chain intact. Copy-only log backups also do not truncate the log, which surprises people who run one and expect space back.

Quick checks

All read-only.

-- 1. Why can't the log truncate? This is the authoritative first question.
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

-- 2. Log usage per database (percentage and absolute sizes).
SELECT
    db.name,
    ls.cntr_value AS log_space_used_pct,
    db.log_reuse_wait_desc
FROM sys.dm_os_performance_counters ls
JOIN sys.databases db ON ls.instance_name = db.name
WHERE ls.counter_name = 'Percent Log Used'
  AND ls.object_name LIKE '%Databases%';

DBCC SQLPERF(LOGSPACE);

-- 3. Backup freshness: last full, diff, and log backup per database.
-- msdb.dbo.backupset is the authoritative record, not job history.
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff_backup,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON d.name = bs.database_name
WHERE d.database_id > 4
  AND d.state_desc = 'ONLINE'
GROUP BY d.name, d.recovery_model_desc
ORDER BY last_log_backup;

-- 4. Where did recent backups actually go, and were they copy-only or snapshots?
SELECT TOP 50
    bs.database_name,
    bs.type,                       -- D=full, I=diff, L=log
    bs.is_copy_only,
    bs.backup_start_date,
    bs.backup_finish_date,
    bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
ORDER BY bs.backup_start_date DESC;

Look for physical_device_name = ‘NUL’ (someone discarded log to force truncation and broke the chain), paths on volumes that no longer exist or are full, and snapshot backups from VSS-based tools that do not truncate.

-- 5. Is an open transaction the real blocker?
DBCC OPENTRAN;

-- 6. Free space on the log volume.
SELECT DISTINCT
    vs.volume_mount_point,
    vs.available_bytes / 1048576 AS available_mb
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
ORDER BY available_mb;

How to diagnose it

flowchart TD
    A[Log usage high or error 9002] --> B{log_reuse_wait_desc?}
    B -->|LOG_BACKUP| C{Last log backup in backupset?}
    B -->|ACTIVE_TRANSACTION| D[DBCC OPENTRAN - find and end it]
    B -->|REPLICATION / AVAILABILITY_REPLICA| E[Check replication agent / AG secondary lag]
    C -->|Recent, regular| F[Backups exist but log still grows - check for NUL backups, tiny backups clearing zero VLFs]
    C -->|Missing or stale| G{Job enabled and succeeding?}
    G -->|Job fine| H[Check backup target: share deleted, volume full, files zero-length]
    G -->|Job broken or gone| I[Re-enable / fix job, take log backup now]
    C -->|Chain broken| J[Take a full backup to restart the chain, then log backups]

  1. Read log_reuse_wait_desc first. Do not touch backup jobs until you know why the log cannot truncate. LOG_BACKUP means a log backup will fix it. ACTIVE_TRANSACTION, REPLICATION, or AVAILABILITY_REPLICA mean the backup chain is not your problem; find the long transaction (DBCC OPENTRAN), the stalled replication agent, or the lagging AG secondary instead. Adding space does not fix any of these.

  2. Verify the backup history in msdb, not the job status. The SQL Agent job can complete “successfully” while writing to a deleted share or a full volume. msdb.dbo.backupset (joined to backupmediafamily) is the authoritative record of what was actually backed up and where. Confirm the last type ‘L’ row is recent and that the file at physical_device_name exists with non-zero size.

  3. Check for a broken chain. If the database was recently restored, detached/attached, or switched from SIMPLE to FULL recovery, the previous chain is gone. The database needs a new full backup before log backups resume meaningfully. Check backupset for the gap in type ‘L’ rows and whether a full backup exists after the switch or restore.

  4. Check for NUL backups. If someone previously “fixed” log growth with BACKUP LOG TO DISK = ‘NUL’, you will see it in backupmediafamily. That backup discarded log records and broke point-in-time recovery from that point. The chain continues afterward only from the next real log backup. Warn whoever did it: TO NUL is not a substitute for TRUNCATE_ONLY (which was removed in SQL Server 2008); the supported escape hatch is switching to SIMPLE recovery.

  5. Rule out the red herring: one log backup did not shrink anything. A log backup truncates the log logically (marks VLFs reusable); it does not shrink the physical file. If log_reuse_wait_desc still shows LOG_BACKUP immediately after a backup, the backup may have cleared zero VLFs because almost nothing was in the inactive portion; the next backup clears it. And if the file itself is now absurdly large, that is a separate shrink decision, not a backup problem.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Percent Log Used per databaseDirect measure of how close the log is to stopping writesAbove 70% with non-NOTHING reuse wait; above 90% is imminent write failure
log_reuse_wait_descTells you exactly why truncation is blockedAnything other than NOTHING persisting across intervals
Time since last log backup (msdb.dbo.backupset, type ‘L’)Recovery point exposure and truncation healthOver 1 hour for a FULL recovery database
Time since last full backup (type ‘D’)Restore capability; also restarts chains after recovery model changesOver 24 hours for production
Log file auto-growth eventsEach event pauses all log-writing transactions; IFI does not apply to log filesAny growth during business hours
VLF count (sys.dm_db_log_info or DBCC LOGINFO)Thousands of tiny VLFs slow recovery, backup, and restoreOver 1000; over 10,000 is severe
Error 9002 in the error logWrites are already failingAny occurrence is a page
Backup destination validityA recorded backup to a dead target is not a backupNUL devices, unreachable shares, zero-length files

Fixes

Log backups stopped running

Take a log backup immediately, then fix the job:

BACKUP LOG [YourDb] TO DISK = N'<your normal backup path>';

Then re-enable or recreate the scheduled log backup job on a cadence that matches your RPO, typically every 15-60 minutes for production FULL recovery databases. Verify the next few scheduled runs land in backupset with valid files. If the job was writing to a bad target, fix the target and confirm free space on the backup volume.

Chain broken after restore, detach/attach, or recovery model switch

Take a full database backup to start a new chain:

BACKUP DATABASE [YourDb] TO DISK = N'<your normal backup path>';

Log backups work from this point forward. Be explicit with the team: recovery is only possible to points after this full backup.

Log is full right now and the disk is also full

If there is no room anywhere for a log backup to truncate into, add a second log file on a different volume as emergency relief, take a log backup, and then remove the temporary file once the log truncates. Do not shrink first; a full log has nothing to shrink until it truncates.

-- Emergency: add a second log file on a volume with free space.
ALTER DATABASE [YourDb] ADD LOG FILE (
    NAME = N'YourDb_log2',
    FILENAME = N'<path on a different volume>\YourDb_log2.ldf',
    SIZE = 1GB,
    FILEGROWTH = 256MB
);

After the log truncates and pressure is off, take a log backup and then remove the emergency file:

-- Remove the emergency log file only after the log has truncated.
-- DBCC SHRINKFILE with EMPTYFILE moves active log out before removal.
DBCC SHRINKFILE (N'YourDb_log2', EMPTYFILE);
ALTER DATABASE [YourDb] REMOVE FILE [YourDb_log2];

Log file physically oversized after the incident

Once the log is truncating normally (log_reuse_wait_desc = NOTHING) and usage is low, you can shrink the file back to a sane pre-allocated size:

-- One-time corrective action. Do not schedule this.
-- Check current size and VLF count before shrinking.
DBCC SHRINKFILE ([YourDb_log], <target_size_MB>);

Repeated shrink/regrow cycles fragment the log into thousands of VLFs and every regrowth zero-initializes (IFI does not apply to log files), stalling writers. Size the file for peak volume between log backups and leave it there.

The database never needed point-in-time recovery

If the honest answer is “we would restore last night’s full backup and accept the data loss,” the database belongs in SIMPLE recovery:

-- WARNING: This breaks the existing log chain. Any differential or log
-- backups taken before this point become unusable for point-in-time restore.
-- Take a full backup immediately after switching.
ALTER DATABASE [YourDb] SET RECOVERY SIMPLE;

SIMPLE truncates the log at checkpoint, so no log backups are needed and this failure mode disappears. The tradeoff is real: you lose point-in-time restore and you break any existing log chain (take a full backup after switching). Do not use this as a panic button on databases that actually have an RPO; use it as a deliberate per-database decision.

Prevention

  • Alert on log backup age, not job status. Query msdb.dbo.backupset for the last type ‘L’ backup per database and alert when it exceeds your RPO. This catches disabled jobs, deleted jobs, and successful-jobs-writing-nowhere in one check.
  • Alert on Percent Log Used above 70% with a non-NOTHING log_reuse_wait_desc. You want to know about truncation blockers hours before error 9002, not during it.
  • Alert on log auto-growth events. Growth during business hours means the log was undersized or truncation is lagging, and every growth event stalls writers while the new space is zero-initialized.
  • Never allow BACKUP LOG TO NUL. Treat physical_device_name = ‘NUL’ in backupmediafamily as a finding. It silently destroys point-in-time recoverability.
  • Match recovery model to actual RPO. Audit databases in FULL recovery. Any database without a working log backup schedule either gets one or moves to SIMPLE.
  • Test restores. RESTORE VERIFYONLY validates media but does not guarantee a restore works. Periodic actual restore tests are the only proof the chain is usable.
  • Watch the chain across operational events. Restores, detach/attach, recovery model changes, and DR drills all restart or break chains. After any of these, verify a fresh full backup exists.

How Netdata helps

  • Log usage and reuse-wait correlation: Netdata’s SQL Server collector surfaces per-database Percent Log Used, so you can see log percent climbing alongside truncation blockers instead of discovering it at error 9002.
  • Backup freshness as a first-class signal: tracking hours since last full and last log backup per database turns “the job said success” into “the authoritative backupset record says nothing landed in 26 hours.”
  • Growth events in context: log file growth correlated with write latency stalls explains the periodic commit hiccups teams usually blame on storage.
  • Volume free space on log and backup targets: catching the backup volume filling up before the job starts “succeeding” into a dead target.
  • Trend over time: a log that creeps upward week over week is a missing-backup problem in slow motion; per-second and historical views make the trend obvious long before the cliff.