Log backup job has not run in FULL or BULK_LOGGED recovery model: SQL Server cannot reuse log space until a transaction log backup confirms those log records are safely stored elsewhere. If the scheduled SQL Agent job or ADF backup activity missed a run, the log fills up within hours on a busy OLTP database.. Long-running or open transaction is blocking log truncation: A BEGIN TRANSACTION that was never committed or rolled back, or a distributed transaction left open by a failed ADF pipeline, holds an active log record that prevents any earlier log space from being reused — even after a log backup runs.. Log file autogrowth is disabled or the disk is full: If the log file has a hard MAXSIZE set (e.g. 10 GB) or the volume it lives on has no free space, autogrowth cannot kick in and the log hits its ceiling. This is common on shared SAN volumes or developer instances with fixed disk quotas.. Replication or Change Data Capture is enabled and lagging: When CDC or transactional replication is configured, the log cannot truncate past the oldest unscanned LSN. If the Log Reader Agent or CDC capture job falls behind — common during high-volume bulk loads — the entire log history accumulates until the agent catches up.. Database mirroring or Availability Group synchronization is paused: In an AG or mirroring setup, log hardening on the secondary must complete before the primary can truncate. If the secondary is unreachable, disconnected, or in SUSPENDED state, the primary log grows unbounded until the secondary reconnects or the session is removed.. Bulk load operation without intermediate checkpoints: A single large BULK INSERT or bcp import inside a FULL recovery database generates a proportionally large log entry. Without intermediate commits or a separate log backup mid-load, the log can fill completely before the operation finishes.. VLF fragmentation causing log space waste: Excessive Virtual Log Files from repeated small autogrowth events can cause SQL Server to report the log as 'full' even when a portion of VLFs are technically inactive — because the log file metadata overhead itself consumes space that cannot be reused without a shrink cycle.

Step 1 — Identify why the log cannot truncate: SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = DB_NAME(); The log_reuse_wait_desc column tells you the exact blocker: LOG_BACKUP (need a backup), ACTIVE_TRANSACTION (open txn), REPLICATION or CDC (capture lag), AVAILABILITY_REPLICA (AG sync stalled), etc. Fix the right cause — not just the symptom.. Step 2 — If log_reuse_wait_desc = LOG_BACKUP, run a log backup immediately: BACKUP LOG [yourdb] TO DISK = 'NUL' WITH STATS = 10; Using NUL frees space right now without needing a real backup device; replace with a real path for retention. After the backup completes, SQL Server marks inactive VLFs as reusable and write operations resume.. Step 3 — If log_reuse_wait_desc = ACTIVE_TRANSACTION, find and kill the blocker: SELECT session_id, start_time, status, command, blocking_session_id, wait_type, open_transaction_count, text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE open_transaction_count > 0; Then KILL ; — confirm with the application owner before killing sessions in production.. Step 4 — If disk or file size is the ceiling, expand the log file or add a secondary log file: ALTER DATABASE [yourdb] MODIFY FILE (NAME = yourdb_log, SIZE = 20480MB, MAXSIZE = UNLIMITED); Or, if the current volume has no space, add a log file on a different drive: ALTER DATABASE [yourdb] ADD LOG FILE (NAME = yourdb_log2, FILENAME = 'D:\logs\yourdb_log2.ldf', SIZE = 5120MB, MAXSIZE = UNLIMITED, FILEGROWTH = 512MB);. Step 5 — If log_reuse_wait_desc = REPLICATION or CDC, check agent lag: EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1; — use only if the publication is being removed or rebuilt. Otherwise restart the Log Reader Agent or CDC capture job and monitor DBCC OPENTRAN([yourdb]) to confirm the oldest active replication LSN is advancing.. Step 6 — If in an Availability Group with a stalled secondary, check replica state: SELECT replica_id, synchronization_state_desc, synchronization_health_desc, log_send_queue_size FROM sys.dm_hadr_database_replica_states; If the secondary is SUSPENDED, resume with: ALTER DATABASE [yourdb] SET HADR RESUME; If the secondary is permanently unavailable, consider removing it from the AG temporarily to unblock the primary log.. Step 7 — After the immediate crisis is resolved, shrink the log file back to a sane size and schedule preventive maintenance: DBCC SHRINKFILE (yourdb_log, 1024); — this reclaims the now-empty space. Then create or verify a SQL Agent job running BACKUP LOG every 15–30 minutes for busy databases, and set log file autogrowth to at least 256 MB increments with MAXSIZE = UNLIMITED.

Low severityresource

SQL Server Error:
9002

Impact

Critical — every write to the database fails the instant error 9002 is raised. ADF Copy and Stored Procedure activities abort with a non-retryable error, Power BI Import refreshes fail on the data read, and any application writing to the database stops cold. SLA breach is immediate; there is no graceful degradation.

All ADF, Fabric, and SSIS pipelines writing to the affected database fail immediately and do not retry by default — ADF marks the activity as Failed (not Retrying) because error 9002 is a resource error, not a transient network issue. Downstream tables that depend on those pipelines receive no new data, causing silent staleness in reports and dashboards. If the pipeline feeds a near-real-time Power BI dataset, refresh failures cascade to SLA breaches within the same hour the log fills.

What does this error mean?

SQL Server error 9002 means the transaction log for a database has reached its maximum allocated size and cannot accept new writes. Every INSERT, UPDATE, DELETE, and DDL statement fails immediately with this error until log space is reclaimed. In a data-pipeline context this surfaces as ADF pipeline failures on Copy or Stored Procedure activities, Fabric dataflow errors, and Power BI dataset refresh failures — all hitting the same database simultaneously. The root cause is almost always a missing log backup in FULL recovery model, a long-running transaction holding the log open, or a disk/file-size ceiling that prevents the log file from growing.

Common causes

1Log backup job has not run in FULL or BULK_LOGGED recovery model: SQL Server cannot reuse log space until a transaction log backup confirms those log records are safely stored elsewhere. If the scheduled SQL Agent job or ADF backup activity missed a run, the log fills up within hours on a busy OLTP database.
2Long-running or open transaction is blocking log truncation: A BEGIN TRANSACTION that was never committed or rolled back, or a distributed transaction left open by a failed ADF pipeline, holds an active log record that prevents any earlier log space from being reused — even after a log backup runs.
3Log file autogrowth is disabled or the disk is full: If the log file has a hard MAXSIZE set (e.g. 10 GB) or the volume it lives on has no free space, autogrowth cannot kick in and the log hits its ceiling. This is common on shared SAN volumes or developer instances with fixed disk quotas.
4Replication or Change Data Capture is enabled and lagging: When CDC or transactional replication is configured, the log cannot truncate past the oldest unscanned LSN. If the Log Reader Agent or CDC capture job falls behind — common during high-volume bulk loads — the entire log history accumulates until the agent catches up.
5Database mirroring or Availability Group synchronization is paused: In an AG or mirroring setup, log hardening on the secondary must complete before the primary can truncate. If the secondary is unreachable, disconnected, or in SUSPENDED state, the primary log grows unbounded until the secondary reconnects or the session is removed.
6Bulk load operation without intermediate checkpoints: A single large BULK INSERT or bcp import inside a FULL recovery database generates a proportionally large log entry. Without intermediate commits or a separate log backup mid-load, the log can fill completely before the operation finishes.
7VLF fragmentation causing log space waste: Excessive Virtual Log Files from repeated small autogrowth events can cause SQL Server to report the log as 'full' even when a portion of VLFs are technically inactive — because the log file metadata overhead itself consumes space that cannot be reused without a shrink cycle.

How to fix it

1Step 1 — Identify why the log cannot truncate: SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = DB_NAME(); The log_reuse_wait_desc column tells you the exact blocker: LOG_BACKUP (need a backup), ACTIVE_TRANSACTION (open txn), REPLICATION or CDC (capture lag), AVAILABILITY_REPLICA (AG sync stalled), etc. Fix the right cause — not just the symptom.
2Step 2 — If log_reuse_wait_desc = LOG_BACKUP, run a log backup immediately: BACKUP LOG [yourdb] TO DISK = 'NUL' WITH STATS = 10; Using NUL frees space right now without needing a real backup device; replace with a real path for retention. After the backup completes, SQL Server marks inactive VLFs as reusable and write operations resume.
3Step 3 — If log_reuse_wait_desc = ACTIVE_TRANSACTION, find and kill the blocker: SELECT session_id, start_time, status, command, blocking_session_id, wait_type, open_transaction_count, text FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE open_transaction_count > 0; Then KILL <session_id>; — confirm with the application owner before killing sessions in production.
4Step 4 — If disk or file size is the ceiling, expand the log file or add a secondary log file: ALTER DATABASE [yourdb] MODIFY FILE (NAME = yourdb_log, SIZE = 20480MB, MAXSIZE = UNLIMITED); Or, if the current volume has no space, add a log file on a different drive: ALTER DATABASE [yourdb] ADD LOG FILE (NAME = yourdb_log2, FILENAME = 'D:\logs\yourdb_log2.ldf', SIZE = 5120MB, MAXSIZE = UNLIMITED, FILEGROWTH = 512MB);
5Step 5 — If log_reuse_wait_desc = REPLICATION or CDC, check agent lag: EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1; — use only if the publication is being removed or rebuilt. Otherwise restart the Log Reader Agent or CDC capture job and monitor DBCC OPENTRAN([yourdb]) to confirm the oldest active replication LSN is advancing.
6Step 6 — If in an Availability Group with a stalled secondary, check replica state: SELECT replica_id, synchronization_state_desc, synchronization_health_desc, log_send_queue_size FROM sys.dm_hadr_database_replica_states; If the secondary is SUSPENDED, resume with: ALTER DATABASE [yourdb] SET HADR RESUME; If the secondary is permanently unavailable, consider removing it from the AG temporarily to unblock the primary log.
7Step 7 — After the immediate crisis is resolved, shrink the log file back to a sane size and schedule preventive maintenance: DBCC SHRINKFILE (yourdb_log, 1024); — this reclaims the now-empty space. Then create or verify a SQL Agent job running BACKUP LOG every 15–30 minutes for busy databases, and set log file autogrowth to at least 256 MB increments with MAXSIZE = UNLIMITED.

Example log output

SqlException: The transaction log for database 'OperationsDB' is full due to 'LOG_BACKUP'. (Microsoft SQL Server, Error: 9002)ADF Activity 'Copy_FactSales' failed: ErrorCode=SqlException, Message=The transaction log for database 'OperationsDB' is full due to 'ACTIVE_TRANSACTION'., Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryExceptionMsg 9002, Level 17, State 4, Line 1 — The transaction log for database 'DW_Staging' is full due to 'REPLICATION'.

Frequently asked questions

SQL Server error 9002 fix — what is the fastest way to free space?

The fastest fix depends on log_reuse_wait_desc. If it shows LOG_BACKUP, run BACKUP LOG [yourdb] TO DISK = 'NUL'; — this takes seconds on most databases and immediately reclaims inactive log space. If it shows ACTIVE_TRANSACTION, find the blocking session with sys.dm_exec_requests and kill it. These two causes cover the majority of 9002 incidents in production environments.

Can SQL Server error 9002 be retried automatically by ADF or SSIS?

No — ADF treats error 9002 as a non-retryable failure because the root cause is a server-side resource exhaustion, not a transient connection issue. Automatic retries will also fail until the log space is actually freed. You must resolve the log issue first, then re-trigger the pipeline manually or wait for the next scheduled run.

9002 transaction log full — do I need to switch to SIMPLE recovery to prevent this?

You can switch, but SIMPLE recovery removes point-in-time restore capability — you can only recover to the last full or differential backup. For any production database handling financial, operational, or compliance data, FULL recovery with a log backup job running every 15–60 minutes is the correct trade-off. SIMPLE recovery is appropriate only for dev/test or databases where the data can be fully reloaded from source.

How do I monitor log space to get an alert before 9002 happens?

Run this query to see current log usage across all databases: SELECT name, log_size_mb = size * 8 / 1024.0, log_used_mb = FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024.0, log_pct_used = CAST(FILEPROPERTY(name, 'SpaceUsed') AS FLOAT) / size * 100 FROM sys.databases; Alert when log_pct_used exceeds 75%. In Azure SQL, use the sql_database_log_percent metric in Azure Monitor. MetricSign surfaces refresh_failed and pipeline_failed incidents as a secondary indicator when log space is exhausted.

Source · learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-9002-database-engine-error