Transaction Log Management

Transaction Log Management is a SQL statement in the Transactions category. Managing transaction log size to prevent it from filling up disk space. Regular log backups are essential. The syntax is BACKUP LOG database_name TO DISK = 'path'; DBCC SHRINKFILE (log_file, target_size);. It returns log management. A typical example: -- Check log size: DBCC SQLPERF(LOGSPACE); -- Backup log to free space: BACKUP LOG MyDatabase TO DISK = 'C:\backup\mydb_log.trn'; -- Shrink log file (use sparingly): DBCC SHRINKFILE (MyDatabase_Log, 100); -- Change recovery model (reduces logging): ALTER DATABASE MyDatabase SET RECOVERY SIMPLE; -- SIMPLE: minimal logging, cannot do point-in-time restore -- FULL: full logging, allows point-in-time restore -- BULK_LOGGED: minimal logging for bulk operations -- PostgreSQL: WAL management -- pg_archivecleanup, wal_keep_size,… A close relative is BEGIN / START TRANSACTION, which begins a new transaction. Changes made within are invisible to others until COMMIT. A close relative is COMMIT, which saves all changes made in the current transaction permanently.