How to Clear Transaction Logs in MS SQL (The transaction log for database is full error)

If you work with SQL Server long enough, you'll eventually run into this issue:

"The transaction log for database is full..."

This happens because SQL Server uses transaction log files (.ldf) to keep track of every database change. Over time, the log file can grow very large. When the log file becomes too large, it can consume server storage and trigger SQL errors.

Following is an example SQL error log where the database transaction log has become full:

Fixing it is pretty straightforward. In this guide, we'll show you how to safely clean and shrink a SQL Server transaction log using a few simple SQL commands.

Identify the Large Log File

Run this query to check which database log files are taking up space:

SELECT DB_NAME(database_id) AS DBName, Name AS Logical_Name, (size*8)/1024 SizeMB, Physical_Name FROM sys.master_files WHERE Name LIKE '%_log%'

In this example, we will be clearing the logs from the ScheduledJobs database, which has around 16 GB of transaction logs.

Change Recovery Mode to SIMPLE

Before shrinking the log file, temporarily switch the database recovery model to SIMPLE using the following query:

USE DATABASE_NAME; ALTER DATABASE DATABASE_NAME SET RECOVERY SIMPLE; GO

Shrink the Transaction Log File

Use the following command to shrink the file:

DBCC SHRINKFILE (LOG_FILE_NAME, 10); GO

Replace LOG_FILE_NAME with the logical log file name returned by the first query.

Switch Recovery Mode Back to FULL

If your database normally uses the FULL recovery model, switch it back after shrinking the log.

ALTER DATABASE DATABASE_NAME SET RECOVERY FULL; GO

After shrinking the log file, run the first query again to confirm the log size has been reduced.

In this instance, we can see the log file has been reduced from 16 GB to around 8 MB.

Note: If your database normally operates in the FULL recovery model, make sure you continue taking regular transaction log backups. Otherwise, the transaction log may continue growing again.

#MSSQL #databases