Skip to content

SQL Server 9002 “log is full due to LOG_BACKUP”: why your ERP stops

  • Home
  • Blog
  • SQL Server 9002 “log is full due to LOG_BACKUP”: why your ERP stops
SQL Server 9002 “log is full due to LOG_BACKUP”: why your ERP stops
Short answer

SQL Server error 9002 means the database’s transaction log has no room for new records; the database stays online but can only be read, so the ERP cannot save invoices, delivery notes or vouchers. The lasting fix is not shrinking the log but removing the obstacle shown in the log_reuse_wait_desc column of sys.databases. The most common cause is LOG_BACKUP: the database uses the FULL recovery model, but no regular transaction log backups are taken.

Symptoms: the ERP cannot save and shows error 9002

  • The ERP screen or the SQL Server error log shows: “The transaction log for database 'ERPDB' is full due to 'LOG_BACKUP'.” In older versions and in Microsoft’s error catalogue the text reads: “The transaction log for database ’…’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.”
  • After “due to” you may also see ACTIVE_TRANSACTION, REPLICATION, AVAILABILITY_REPLICA, ACTIVE_BACKUP_OR_RESTORE or CHECKPOINT instead of LOG_BACKUP; each points to a different root cause.
  • Reports and lists still open, but saving, imports and period-end routines fail, because a database with a full log runs read-only.
  • The .ldf log file on the server has grown to tens or even hundreds of GB, and often the disk holding it is full as well.
  • If the server was restarted while the log was full, the database may be stuck in RESOURCE PENDING and the ERP cannot connect at all.

Root cause: why can’t the log be reused?

SQL Server writes every change to the transaction log first. For old records in the log to be reused, a logical clean-up called truncation must happen. Under the SIMPLE recovery model, truncation happens automatically after each checkpoint. Under FULL or BULK_LOGGED, it only happens after a transaction log backup. So if a database in FULL recovery never gets a log backup, the log grows until the disk runs out.

In ERP environments it typically unfolds like this: in SQL Server Standard and Enterprise editions new databases are created in FULL recovery by default (Express defaults to SIMPLE). According to Microsoft, a new database in FULL recovery reuses its log like SIMPLE until the first full backup is taken. Once someone takes a full backup, often a backup tool, the log chain starts, and from then on the log is only cleared by log backups. Because nobody scheduled log backups, the log grows silently for months until the disk or the file’s MAXSIZE limit is reached and 9002 appears.

The other causes follow the same logic. ACTIVE_TRANSACTION shows that a long-open transaction, such as an interrupted bulk import or an uncommitted session, is pinning the log; this grows the log even under SIMPLE. REPLICATION and AVAILABILITY_REPLICA mean log records have not yet reached the distributor or a secondary replica, and ACTIVE_BACKUP_OR_RESTORE means a running data backup or restore is holding truncation back. Copy-only log backups do not truncate the log either.

Step-by-step fix

  1. Identify the obstacle first. The query below shows each database’s recovery model and why its log cannot be reused; it only reads data.
  2. Check log usage and recent backups. If the msdb backup history has no type = ’L’ rows, or the last log backup is very old, the cause is almost certainly LOG_BACKUP.
  3. If it is LOG_BACKUP, take a transaction log backup. Write it not to the full disk holding the log but to another disk or network share with enough space. If the log has never been backed up, Microsoft notes that two log backups are needed before the log can be truncated to the point of the last backup.
  4. If it is ACTIVE_TRANSACTION, find the open transaction. DBCC OPENTRAN shows the oldest active transaction and its session. If it belongs to an ERP user or an import service, first try to finish it properly; KILL is a last resort, and rolling back a large transaction can take a long time.
  5. If the disk is completely full, even a log backup may fail. Move unneeded files off that disk, or temporarily add a second log file on another disk (ALTER DATABASE … ADD LOG FILE). Microsoft recommends multiple log files only as a temporary measure.
  6. If autogrow is disabled for the log file or its MAXSIZE limit is reached, check sys.master_files and raise the limit.
  7. After truncation the log is empty inside, but the file keeps its physical size. If it grew once to an extreme size and you genuinely need the disk space, a single shrink to a sensible target size is acceptable.
  8. If the database was in recovery when 9002 occurred and shows RESOURCE PENDING, bring it online with ALTER DATABASE … SET ONLINE once space is available.
-- 1) Obstacle and recovery model (read-only)
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

-- 2) Log usage (run in the affected database)
USE [ERPDB];
SELECT total_log_size_in_bytes / 1048576 AS total_mb,
       used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

-- 3) Recent backups (D = full, I = differential, L = log)
SELECT TOP (10) database_name, type, backup_finish_date
FROM msdb.dbo.backupset
WHERE database_name = 'ERPDB'
ORDER BY backup_finish_date DESC;
-- LOG_BACKUP: back up the log (target: another disk with free space)
BACKUP LOG [ERPDB]
    TO DISK = 'F:\SQLBackup\ERPDB_LOG.trn';

-- ACTIVE_TRANSACTION: find the oldest open transaction
DBCC OPENTRAN ('ERPDB');

-- Size, growth and limit settings of log files
SELECT DB_NAME(database_id) AS db, name, physical_name,
       size * 8 / 1024 AS size_mb, max_size, growth
FROM sys.master_files
WHERE type_desc = 'LOG';

Shrinking is not a fix. In Microsoft’s words, shrinking a log file alone cannot solve the problem of a full log; you must first find out why the log cannot be truncated. A shrunk log grows again under the same workload and causes performance overhead while it grows. If a one-off shrink is needed, do it after truncation and outside business hours.

-- Only after truncation, one-off; target size in MB
USE [ERPDB];
DBCC SHRINKFILE (N'ERPDB_log', 4096);

In an emergency, switching the database to SIMPLE clears the log at the next checkpoint, but it breaks the log backup chain and removes the ability to restore to a point in time before that moment. Microsoft recommends backing up the log before leaving FULL, and taking a full or differential backup immediately after switching back from SIMPLE to FULL to restart the chain. Make this decision together with someone who knows your backup policy.

Preventing recurrence

  • Choose the recovery model deliberately: if you need to restore to a specific time during the day, use FULL and schedule regular log backups; if last night’s full backup is enough, use SIMPLE.
  • Under FULL, schedule log backups through a SQL Server Agent job or your backup software at a frequency that matches transaction volume. Make sure your backup tool does not take log backups as copy-only.
  • Set log autogrowth to a fixed size rather than a percentage; Microsoft recommends not setting FILEGROWTH above 1,024 MB for transaction logs.
  • Monitor log usage with sys.dm_db_log_space_usage together with free disk space, and alert when a threshold is crossed.
  • Measure how much overnight imports, index rebuilds and period-end jobs grow the log, and size the log accordingly.
  • Where possible, keep log files on a separate disk from data files, and never place them on compressed file systems.

When to call an expert

If log_reuse_wait_desc shows REPLICATION or AVAILABILITY_REPLICA, if the database is stuck in RESOURCE PENDING, if the disk is so full that not even a log backup can be written, or if you are unsure how changing the recovery model affects your backup policy, a wrong step can cause data loss. A significant share of the ERP outages we see in the field at businesses in Denizli stem from this kind of missing maintenance. As part of its ERP and ERP support service, Workas carries out diagnosis, log and backup planning and monitoring for SQL Server-based ERP systems, Monday to Saturday from 08:00 to 18:00, with priority response in emergencies.

Frequently Asked Questions

If I delete the log file, will the ERP work again?

No. Microsoft explicitly warns never to delete or move the log unless you fully understand the consequences. Without its log the database can become inconsistent and may not open.

Does switching to SIMPLE lose data?

It does not delete existing data, but it breaks the log backup chain. From then on you can only restore to the moment of the last full or differential backup, not to a specific time of day.

I took a log backup but the file did not get smaller. Why?

A log backup empties the log internally (truncation); it does not shrink the physical file. The file size only drops with DBCC SHRINKFILE, which should be done only when necessary and as a one-off.

How often should I back up the log?

It depends on how much data loss you can accept and on transaction volume. The more often you back up, the smaller the log stays and the lower the risk of loss.

Sources

  1. Troubleshoot a full transaction log (SQL Server Error 9002) – Microsoft Learn
  2. MSSQLSERVER_9002 – Microsoft Learn
  3. The transaction log (SQL Server) – Factors that can delay log truncation – Microsoft Learn
  4. Recovery models (SQL Server) – Microsoft Learn
  5. Manage the size of the transaction log file – Microsoft Learn

Let's Get Started

Let's Work Together!

Let's plan your company's IT needs together — we'll quickly find the right solution for you.

x

Since 2002, Workas Software Technology has provided businesses in Denizli with ERP, cybersecurity, network, server and backup, custom software and AI solutions.

Address
Kınıklı, Hüseyin Yılmaz Cd. No:67,
B Blok Z06, 20160 Pamukkale/Denizli, Türkiye
Get in Touch
0549 669 99 06
0535 089 29 29
Working Hours
Mon - Sat: 8:00 - 18:00