How to Resolve the “transaction log file full” issue?


What is a transaction log?


Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction.


Error what you see?
The transaction log for database 'xxxxxxxxxx' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases


Issues arise when the log file is full?


Most common reasons are?
Your database is set to Full Recovery and no Tlog backups are happening
Large inserts or deletes are happening (and it really needs to be that big)
Long running transactions are running (index maintenance or bulk import)
Other Reason(s)
If Always-on, Replication and Mirroring Fails


How to troubleshoot?


SELECT name,log_reuse_wait_desc
FROM sys.databases

DBCC SQLPERF(logspace)
DBCC Opentran
Note: SQL Server itself actually tells us what is going on with the log files in the log_reuse_wait_desc column of the sys.databases catalog view.

свернуть