The transaction log for database ‘xxxxx’ is full due to ‘ACTIVE_TRANSACTION’.
- Backup the transaction log.
- Set database recovery model from “Full” to “Simple”.
- If database setup for Always On, make sure Always On is working and synchronizing on all secondary database servers.
Backup Transaction Log
BACKUP DATABASE [MyDB] TO DISK = N'D:\DBBackups\FULL\MyDB_Full_2020.bak' WITH NOFORMAT, NOINIT, NAME = N'Full Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [MyDB] TO DISK = N'D:\DBBackups\LOGS\MyDB_LOG_2020_01.trn' WITH NOFORMAT, NOINIT, NAME = N'Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO Use [MyDB] DBCC SHRINKFILE (MyDB_log, 100);
If you don’t care about the transaction log backup file, just set the file to “nul”, not “null”.
-- Set to NUL, not NULL BACKUP LOG [MyDB] TO DISK='NUL' WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR Use [MyDB] DBCC SHRINKFILE (MyDB_log, EMPTYFILE);
- The size of the transaction log backup file is the size of the current transaction log file.
- If you don’t care about the transaction log backup file, just set the file to null.
- You might ran into trouble shrinking log files for database involved in Always On. Might have to remove the database from Always On before you can shrink the log file.
- You might need to do a full database backup first, then do a transaction log backup, then shrink the log file.
- Make sure there is enough space for the transaction logs. Also check the log max limit. The log max limit may be auto set by system.
- If the database is part of Always On, make sure there is enough space for the transaction logs on all servers in the cluster.
Transaction Log Info
select name, log_reuse_wait_desc from sys.databases DBCC LOGINFO;