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.
Open SQL Server Management Studio (SSMS) and connect to your instance.
Expand the Databases folder and select the affected database.
Right-click the database and select Properties.
Select the Files page.
Identify the log file (usually ending in
.ldf) and check the Size and Initial Size columns.Check the available disk space on the drive where the
.ldffile 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.
In SSMS, right-click the database and select Properties.
Select the Options page.
Locate the Recovery model setting.
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.
Perform a transaction log backup using SSMS or a maintenance script.
If the log remains full after a backup, the log file may have a maximum size limit.
In the Options page of the database properties, check the Maximum size for the log file.
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.
Check for long-running transactions using the following query:
SELECT name, log_reuse_wait_desc FROM sys.databases;
If
log_reuse_wait_descshowsNOT_SUPPORTED, the log should clear after the current transaction completes.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_descfor the database returnsNOT_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.
