[SQL Server] Error 9002 The transaction log for database X is full

Have you ever encountered this error 9002 on your SQL Server instance ?

When the database cannot write to the transaction log because it is full it generates the 9002 error. It is important to determine the root cause of the error :

• there is no more free space in the transaction log and it has reached its maximum size limit

• there is no more free space in the transaction log and it cannot grow because there is no more free space left on the disk

• a long running transaction is preventing log truncation

To determine what is preventing log truncation, execute the following query :

Solution :

To manage a full transaction log, the most common corrective actions :

• Adding a second transaction log to the database

• Backing up the transaction log

• Freeing up disk space on the disk volume

• Increasing the size of the disk volume

• Increasing the size of the log file

• Killing a long running transaction

• Moving the log to another disk that has sufficient capacity

• Truncating the transaction log

The DBCC OPENTRAN command displays information about the oldest active transaction within the transaction log of a database.