トランザクションログ / undo の肥大化

症状

トランザクションログファイルまたは undo/redo 領域が急速に増大している。'log full'、'ORA-01555 snapshot too old'、'unable to extend undo'。

1. ログを増大させている開いたトランザクションを見つける

最も古いアクティブトランザクションが終了するまで、ログ/undo はクリアできない。単一の長時間開いたトランザクションがログ全体を固定していることがある。

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. エンジン固有の根本原因を把握する

MSSQL: FULL 復旧モデルでは、ログバックアップを取らないとログは際限なく増大する。Oracle: ORA-01555 = undo の保持期間/サイズ不足 + 長いクエリ。PG: レプリケーションスロットが進まないと WAL が蓄積し、長いトランザクションは VACUUM をブロックする。

3. MSSQL ログに対する正しい対処

ログバックアップ (BACKUP LOG) を取る — これでログが再利用可能になる。復旧モデルが本当に SIMPLE であるべきなら変更する。その後、必要なら (必要な場合のみ) ログを圧縮する。

SQL Server

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

4. PG の WAL / レプリケーションスロットの確認

進まない (非アクティブな) レプリケーションスロットは WAL の蓄積を引き起こす。削除する前にスロットの所有者 (slot_type、database、plugin) を確認する: 一時的に切断されたスタンバイや、論理サブスクライバー/CDC (例: Debezium) かもしれない。スロットを削除すると、接続していたスタンバイは再構築が必要になり、サブスクライバー/CDC 側は再同期が必要になる。本当に未使用の場合のみ削除する。恒久的な保護には 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. undo/保持期間を調整し、再発を防止する

Oracle: UNDO 表領域を拡張し、長いクエリに合わせて undo_retention を設定する (または RETENTION GUARANTEE)。アプリケーションで長時間開いたトランザクションを防ぐ。ログバックアップジョブが実行されていることを監視する。