Transaction log stuck at 100%

Hi @Mirajul

Thank you for contacting Microsoft Q&A. Please find below the detailed steps to address the reported issue.

Is the database Azure SQL Database (single DB/elastic pool), or Azure SQL Managed Instance / SQL Server? Replication is not supported on Azure SQL Database, so a REPLICATION wait there almost always means CDC (or its remnants) is involved. [learn.microsoft.com],

REPLICATION in sys.databases.log_reuse_wait_desc doesn’t necessarily mean you configured transactional replication. SQL’s log manager only knows that a log-scanning consumer is registered and hasn’t advanced the log—that consumer can be CDC (which uses replication’s log reader internally) or actual transactional replication. If the capture/log reader can’t advance (stopped, misconfigured, or orphaned), log truncation is blocked, checkpoints fail (5901), and the log stays 100%.[learn.microsoft.com]

A) Azure SQL Database (single DB / elastic pool)

Context: Azure SQL Database doesn’t support transactional replication; CDC is implemented by an internal scheduler (no SQL Agent). Seeing REPLICATION here points to CDC capture/cleanup latency, disabled/partial CDC, or leftover CDC artifacts. [learn.microsoft.com]

1. Verify whether CDC is enabled (and if the scheduler is healthy)

SQL

SELECT name, is_cdc_enabled, log_reuse_wait_desc

FROM sys.databases

WHERE name = DB_NAME();

-- Optional visibility into CDC log scanning sessions

SELECT * FROM sys.dm_cdc_log_scan_sessions

ORDER BY start_time DESC;

• If is_cdc_enabled = 1, CDC is holding up truncation until capture runs and advances the LSN.
• If is_cdc_enabled = 0 but you still see REPLICATION, there may be orphaned CDC artifacts blocking log reuse. [learn.microsoft.com]

1. If CDC is enabled but stuck: nudge capture/cleanup and then, if not required, disable CDC cleanly
   • Temporarily scale up the database (more log worker headroom) and let the CDC scheduler catch up.
     • If CDC is not needed, disable CDC after capture completes:

SQL

EXEC sys.sp_cdc_disable_db;

• Important: If the log is 100% full, the disable operation itself can fail because it must record metadata changes in the log. In that case, increase available log capacity (scale up) so the statement can succeed, then re‑check log_reuse_wait_desc.

1. If CDC is already disabled and REPLICATION persists:
   • Re‑enable CDC and then disable again (forces the scheduler to reconcile artifacts), or enumerate and remove any lingering capture instances (table‑level) before disabling at DB level.
     • Re‑check:

SQL

SELECT log_truncation_holdup_reason

FROM sys.database_recovery_status

WHERE database_id = DB_ID();

 

SELECT * FROM sys.dm_db_log_space_usage;

• If log_truncation_holdup_reason still indicates a replication/CDC holdup with no CDC enabled, open a support ticket—this is the “orphaned replication recovery unit” condition which occasionally needs platform-side cleanup. Provide the Holdup LSN (the one you shared) and timestamps. [learn.microsoft.com]

1. Eliminate unrelated blockers (do this regardless)
   • Long running transactions will also block truncation even if CDC is healthy:

SQL

DBCC OPENTRAN; -- identifies the oldest active transaction

• Microsoft’s long‑running transaction monitor (below) helps identify and, if necessary, kill the oldest blocker (set @kill_oldest_tran = 1):

SQL

-- Microsoft’s T-SQL monitor for long transactions on CDC systems

-- (excerpt from the official guidance; adjust thresholds as noted)

DECLARE @kill_oldest_tran BIT = 0; -- set to 1 only if agreed

-- … full script omitted here for brevity …

Guidance + full script: Transaction log grows due to long-running transactions and CDC

1. Confirm recovery

SQL

SELECT name, log_reuse_wait_desc

FROM sys.databases WHERE name = DB_NAME();

 

SELECT * FROM sys.dm_db_log_space_usage;

Expect log_reuse_wait_desc = NOTHING (or a transient LOG_BACKUP that clears automatically on Azure SQL DB), and the log used percent dropping.

Useful KB/Docs for the customer

• Azure SQL DB log troubleshooting overview: Troubleshoot transaction log errors with Azure SQL Database
• CDC Overview for Azure SQL DB (scheduler-based capture/cleanup): Change data capture (CDC) with Azure SQL Database
• CDC & log growth (monitor long transactions): Transaction log grows due to long-running transactions and CDC

B) Azure SQL Managed Instance / SQL Server

Context: MI/SQL Server do support transactional replication. If you previously had replication or restored from a published DB, you can end up with orphaned replication/CDC metadata that blocks log truncation.

1. Check what’s actually enabled

SQL

SELECT name, is_published, is_subscribed, is_cdc_enabled, log_reuse_wait_desc

FROM sys.databases

WHERE name = DB_NAME();

1. If you’re not using replication but is_published = 1 or the DB was restored from a published copy: clean up replication metadata

SQL

-- Remove replication from the database (safe when replication isn't in use)

EXEC sp_removedbreplication @dbname = DB_NAME();

Re‑check log_reuse_wait_desc.

1. If CDC is enabled and capture isn’t advancing: either fix the CDC capture job (SQL Agent) or disable CDC cleanly when not needed:

SQL

EXEC sys.sp_cdc_disable_db;

If the disable fails because the log is full, add log space (growth), then retry.

1. Emergency only (you must be sure replication isn’t required): Mark all pending transactions as “distributed” so log truncation can proceed:

SQL

-- Emergency reset: mark all transactions as distributed

EXEC sys.sp_repldone

@xactid = NULL,

@xact_seqno= NULL,

@numtrans = 0,

@time = 0,

@reset = 1;

Caution: This tells SQL Server to skip any pending replication capture of those transactions—use only when you’ve confirmed there is no active replication and you’re clearing an orphaned state. Docs: sp_repldone (Transact‑SQL)

1. Validate and shrink (MI/SQL Server)

• Back up the log (MI/SQL Server) to force truncation, then shrink carefully to a sane size:

SQL

BACKUP LOG [YourDB] TO DISK = 'https://<storage-url>/yourdb_log.bak' WITH COMPRESSION;

DBCC SHRINKFILE (YourDB_log, <targetMB>);

General 9002 guidance: Troubleshoot a full transaction log (SQL Server Error 9002)

Useful KB/Docs for the customer

• Replication SPs & sp_repldone: Replication stored procedures

Why error 5901 appears here

When log reuse is blocked (by CDC/replication or even slow I/O/corruption), CHECKPOINT can fail with 5901. In SQL Server 2016 there’s a specific fix when long I/Os/latch timeouts trigger repeated 5901 at a given offset (KB 4476977), but in your case the REPLICATION holdup is the primary driver—solve that and checkpoint succeeds. KB (for completeness): KB4476977 – FIX: Error 5901 occurs on the same offset…