Gonflement du journal de transactions / de l'undo

Symptôme

Le fichier du journal de transactions ou la zone undo/redo grossit rapidement ; 'log full', 'ORA-01555 snapshot too old', 'unable to extend undo'.

1. Trouvez la transaction ouverte qui fait grossir le log

Le log/l'undo ne peut pas être libéré tant que la plus ancienne transaction active n'est pas terminée. Une seule longue transaction ouverte peut retenir tout le 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. Comprenez la cause profonde propre au moteur

MSSQL : en mode de récupération FULL, le log croît indéfiniment si aucune sauvegarde du log n'est effectuée. Oracle : ORA-01555 = rétention/taille d'undo insuffisante + requête longue. PG : si un slot de réplication n'avance pas, le WAL s'accumule ; une longue transaction bloque VACUUM.

3. La bonne action pour le log MSSQL

Faites une sauvegarde du log (BACKUP LOG) : le log redevient réutilisable. Si le mode de récupération doit vraiment être SIMPLE, changez-le. Ensuite, si besoin (et seulement si besoin), réduisez le log.

SQL Server

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

4. Vérification du WAL / des slots de réplication PG

Un slot de réplication qui n'avance pas (inactif) provoque une accumulation de WAL. Avant de le supprimer, vérifiez à qui appartient le slot (slot_type, database, plugin) : il peut s'agir d'une standby temporairement déconnectée ou d'un abonné logique/CDC (par ex. Debezium). Si le slot est supprimé, la standby rattachée doit être reconstruite et l'abonné/la CDC resynchronisé. Ne le supprimez que s'il est réellement inutilisé ; pour une protection durable, fixez une limite avec 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. Ajustez l'undo/la rétention et évitez la récidive

Oracle : agrandissez le tablespace UNDO, réglez undo_retention selon les requêtes longues (ou RETENTION GUARANTEE). Évitez les longues transactions ouvertes dans l'application. Surveillez l'exécution du job de sauvegarde du log.