Skip to main content

How to Troubleshoot a Microsoft SQL Server Transaction Log Full Error

A "Transaction Log Full" error occurs when the Microsoft SQL Server transaction log file reaches its maximum size or when the disk space containing the log file is exhausted. This prevents the database from processing new transactions until the log is...

A "Transaction Log Full" error occurs when the Microsoft SQL Server transaction log file reaches its maximum size or when the disk space containing the log file is exhausted. This prevents the database from processing new transactions until the log is cleared or expanded.

Before you begin

You will need:

  • A Microsoft SQL Server database.

  • Access to SQL Server Management Studio (SSMS).

  • Administrative permissions for the SQL Server instance.

Check the log file size and disk space

The first step is to determine if the error is caused by a lack of physical disk space or by a configuration limit on the log file size.

  1. Open SQL Server Management Studio (SSMS) and connect to your instance.

  2. Expand the Databases folder and select the affected database.

  3. Right-click the database and select Properties.

  4. Select the Files page.

  5. Identify the log file (usually ending in .ldf) and check the Size and Initial Size columns.

  6. Check the available disk space on the drive where the .ldf file is located.

Note: If the disk is full, the log cannot grow even if the software limit has not been reached.

Check the recovery model

The behavior of the transaction log depends on the database's recovery model.

  1. In SSMS, right-click the database and select Properties.

  2. Select the Options page.

  3. Locate the Recovery model setting.

  4. Note whether it is set to Full, Simple, or Bulk-logged.

Important: If the recovery model is set to Full, the transaction log will keep all transactions until they are backed up. If the model is set to Simple, the log is truncated automatically after each transaction.

Resolve the issue based on the recovery model

If the recovery model is Full

If the database is in Full recovery mode, the log will remain full until a transaction log backup is performed.

  1. Perform a transaction log backup using SSMS or a maintenance script.

  2. If the log remains full after a backup, the log file may have a maximum size limit.

  3. In the Options page of the database properties, check the Maximum size for the log file.

  4. If a limit is set, increase the limit or set it to Unlimited.

If the recovery model is Simple

If the database is in Simple recovery mode and the log is full, it usually indicates a very large transaction is currently running or the disk is full.

  1. Check for long-running transactions using the following query:

    SELECT name, log_reuse_wait_desc FROM sys.databases;

  2. If log_reuse_wait_desc shows NOT_SUPPORTED, the log should clear after the current transaction completes.

  3. If the disk is full, move the log file to a larger partition or expand the existing partition.

Confirm the result

  • The "Transaction Log Full" error no longer appears when performing database operations.

  • The log_reuse_wait_desc for the database returns NOT_SUPPORTED (for Simple mode) or the log size remains stable after a backup (for Full mode).

Limitations

  • This procedure does not address issues caused by hardware failure or corrupted log files.

  • Changing the recovery model from Full to Simple will prevent you from performing point-in-time restores.

  • If you are using a managed database service, some configuration changes may be restricted by your service provider.

Did this answer your question?