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 eat up 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 DB which seems to be having 16GB of 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

Switch Recovery Mode Back to FULL

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 files has been reduced to 8MB from 16 GB.