Transaction log / undo bloat

Symptom

The transaction log file or the undo/redo area is growing fast; 'log full', 'ORA-01555 snapshot too old', 'unable to extend undo'.

1. Find the open transaction growing the log

The log/undo cannot be cleared until the oldest active transaction finishes. A single long open transaction may be pinning the entire log.

Oracle

SELECT s.sid, s.username, t.start_time, t.used_ublk FROM v$transaction t JOIN v$session s ON t.ses_addr=s.saddr ORDER BY t.start_time;

PostgreSQL

SELECT pid, state, xact_start, now()-xact_start age, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;

MySQL

SELECT trx_id, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx ORDER BY trx_started;

SQL Server

DBCC OPENTRAN;

2. Understand the engine-specific root cause

MSSQL: in the FULL recovery model, the log grows indefinitely if no log backups are taken. Oracle: ORA-01555 = insufficient undo retention/size + a long query. PG: if a replication slot does not advance, WAL accumulates; a long transaction blocks VACUUM.

3. The right action for the MSSQL log

Take a log backup (BACKUP LOG) — this makes the log reusable. If the recovery model really should be SIMPLE, change it. Then, if needed (and only if needed), shrink the log.

SQL Server

BACKUP LOG [DB] TO DISK='C:\backup\DB_log.trn';
-- Then, if needed: DBCC SHRINKFILE (N'DB_log', 512);

4. PG WAL / replication slot check

A replication slot that does not advance (inactive) causes WAL accumulation. Before dropping it, verify the slot's owner (slot_type, database, plugin): it may be a temporarily disconnected standby or a logical subscriber/CDC (e.g. Debezium). If the slot is dropped, the attached standby must be rebuilt and the subscriber/CDC side must be resynchronized. Drop it only if it is truly unused; for lasting protection, set an upper bound with max_slot_wal_keep_size (PG13+).

PostgreSQL

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) retained FROM pg_replication_slots ORDER BY 3 DESC;
-- If unneeded: SELECT pg_drop_replication_slot('slot_name');

5. Tune undo/retention and prevent recurrence

Oracle: grow the UNDO tablespace, set undo_retention according to long queries (or RETENTION GUARANTEE). Prevent long open transactions in the application. Monitor that the log backup job runs.