Transaction Log is Full

Error:

The transaction log for database ‘xxxxx’ is full due to ‘ACTIVE_TRANSACTION’.

Fix:

• 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);

Note:

• 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;