Aufblähen von Transaktionsprotokoll/Undo

Symptom

Die Datei des Transaktionsprotokolls oder der Undo-/Redo-Bereich wächst schnell; 'log full', 'ORA-01555 snapshot too old', 'unable to extend undo'.

1. Die offene Transaktion finden, die das Log wachsen lässt

Log/Undo kann erst bereinigt werden, wenn die älteste aktive Transaktion beendet ist. Eine einzige lang offene Transaktion kann das gesamte Log festhalten.

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. Engine-spezifische Ursache verstehen

MSSQL: Im Recovery-Modell FULL wächst das Log unbegrenzt, wenn keine Log-Backups erstellt werden. Oracle: ORA-01555 = unzureichende Undo-Retention/-Größe + eine lange Abfrage. PG: Rückt ein Replication Slot nicht vor, sammelt sich WAL an; eine lange Transaktion blockiert VACUUM.

3. Die richtige Maßnahme für das MSSQL-Log

Erstelle ein Log-Backup (BACKUP LOG) – dadurch wird das Log wiederverwendbar. Soll das Recovery-Modell tatsächlich SIMPLE sein, ändere es. Verkleinere das Log danach bei Bedarf (und nur bei Bedarf).

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 prüfen

Ein Replication Slot, der nicht vorrückt (inaktiv), führt zur Ansammlung von WAL. Prüfe vor dem Löschen den Besitzer des Slots (slot_type, database, plugin): Es kann eine vorübergehend getrennte Standby oder ein logischer Subscriber/CDC (z. B. Debezium) sein. Wird der Slot gelöscht, muss die angeschlossene Standby neu aufgebaut und die Subscriber-/CDC-Seite neu synchronisiert werden. Lösche ihn nur, wenn er wirklich ungenutzt ist; zum dauerhaften Schutz setze mit max_slot_wal_keep_size (PG13+) eine Obergrenze.

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/Retention abstimmen und Wiederholung verhindern

Oracle: Vergrößere den UNDO-Tablespace, setze undo_retention passend zu langen Abfragen (oder RETENTION GUARANTEE). Verhindere lang offene Transaktionen in der Anwendung. Überwache, dass der Log-Backup-Job läuft.