How do you clear the SQL Server transaction log?

DISCLAIMER: Please read comments below carefully, and I assume you've already read the accepted answer. As I said nearly 5 years ago:

if anyone has any comments to add for situations when this is NOT an adequate or optimal solution then please comment below

• Right click on the database name.

• Select Tasks → Shrink → Database

• Then click OK!

I usually open the Windows Explorer directory containing the database files, so I can immediately see the effect.

I was actually quite surprised this worked! Normally I've used DBCC before, but I just tried that and it didn't shrink anything, so I tried the GUI (2005) and it worked great - freeing up 17 GB in 10 seconds

In Full recovery mode this might not work, so you have to either back up the log first, or change to Simple recovery, then shrink the file. [thanks @onupdatecascade for this]

--

PS: I appreciate what some have commented regarding the dangers of this, but in my environment I didn't have any issues doing this myself especially since I always do a full backup first. So please take into consideration what your environment is, and how this affects your backup strategy and job security before continuing. All I was doing was pointing people to a feature provided by Microsoft!

Here is a simple and very inelegant & potentially dangerous way.

1. Backup DB
2. Detach DB
3. Rename Log file
4. Attach DB
5. New log file will be recreated
6. Delete Renamed Log file.

I'm guessing that you are not doing log backups. (Which truncate the log). My advice is to change recovery model from full to simple. This will prevent log bloat.

If you do not use the transaction logs for restores (i.e. You only ever do full backups), you can set Recovery Mode to "Simple", and the transaction log will very shortly shrink and never fill up again.

If you are using SQL 7 or 2000, you can enable "truncate log on checkpoint" in the database options tab. This has the same effect.

This is not recomended in production environments obviously, since you will not be able to restore to a point in time.

This technique that John recommends is not recommended as there is no guarantee that the database will attach without the log file. Change the database from full to simple, force a checkpoint and wait a few minutes. The SQL Server will clear the log, which you can then shrink using DBCC SHRINKFILE.

To Truncate the log file:

• Backup the database
• Detach the database, either by using Enterprise Manager or by executing : Sp_DetachDB [DBName]
• Delete the transaction log file. (or rename the file, just in case)
• Re-attach the database again using: Sp_AttachDB [DBName]
• When the database is attached, a new transaction log file is created.

To Shrink the log file:

• Backup log [DBName] with No_Log

• Shrink the database by either:

  Using Enterprise manager :- Right click on the database, All tasks, Shrink database, Files, Select log file, OK.

  Using T-SQL :- Dbcc Shrinkfile ([Log_Logical_Name])

You can find the logical name of the log file by running sp_helpdb or by looking in the properties of the database in Enterprise Manager.

First check the database recovery model. By default, SQL Server Express Edition creates a database for the simple recovery model (if I am not mistaken).

Backup log DatabaseName With Truncate_Only:

DBCC ShrinkFile(yourLogical_LogFileName, 50)

SP_helpfile will give you the logical log file name.

Refer to:

Recover from a full transaction log in a SQL Server database

If your database is in Full Recovery Model and if you are not taking TL backup, then change it to SIMPLE.

It happened with me where the database log file was of 28 GBs.

What can you do to reduce this? Actually, log files are those file data which the SQL server keeps when an transaction has taken place. For a transaction to process SQL server allocates pages for the same. But after the completion of the transaction, these are not released suddenly hoping that there may be a transaction coming like the same one. This holds up the space.

Step 1: First Run this command in the database query explored checkpoint

Step 2: Right click on the database Task> Back up Select back up type as Transaction Log Add a destination address and file name to keep the backup data (.bak)

Repeat this step again and at this time give another file name

Step 3: Now go to the database Right-click on the database

Tasks> Shrinks> Files Choose File type as Log Shrink action as release unused space

Step 4:

Check your log file normally in SQL 2014 this can be found at

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014EXPRESS\MSSQL\DATA

In my case, its reduced from 28 GB to 1 MB

To my experience on most SQL Servers there is no backup of the transaction log. Full backups or differential backups are common practice, but transaction log backups are really seldom. So the transaction log file grows forever (until the disk is full). In this case the recovery model should be set to "simple". Don't forget to modify the system databases "model" and "tempdb", too.

A backup of the database "tempdb" makes no sense, so the recovery model of this db should always be "simple".

Try this:

USE DatabaseName

GO

DBCC SHRINKFILE( TransactionLogName, 1)

BACKUP LOG DatabaseName WITH TRUNCATE_ONLY

DBCC SHRINKFILE( TransactionLogName, 1)

GO 

Slightly updated answer, for MSSQL 2017, and using the SQL server management studio. I went mostly from these instructions https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/

I had a recent db backup, so I backed up the transaction log. Then I backed it up again for good measure. Finally I shrank the log file, and went from 20G to 7MB, much more in line with the size of my data. I don't think the transaction logs had ever been backed up since this was installed 2 years ago.. so putting that task on the housekeeping calendar.

Some of the other answers did not work for me: It was not possible to create the checkpoint while the db was online, because the transaction log was full (how ironic). However, after setting the database to emergency mode, I was able to shrink the log file:

alter database <database_name> set emergency;
use <database_name>;
checkpoint;
checkpoint;
alter database <database_name> set online;
dbcc shrinkfile(<database_name>_log, 200);

DB Transaction Log Shrink to min size:

1. Backup: Transaction log
2. Shrink files: Transaction log
3. Backup: Transaction log
4. Shrink files: Transaction log

I made tests on several number of DBs: this sequence works.

It usually shrinks to 2MB.

OR by a script:

DECLARE @DB_Name nvarchar(255);
DECLARE @DB_LogFileName nvarchar(255);
SET @DB_Name = '<Database Name>';               --Input Variable
SET @DB_LogFileName = '<LogFileEntryName>';         --Input Variable
EXEC 
(
'USE ['+@DB_Name+']; '+
'BACKUP LOG ['+@DB_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2) ' +
'BACKUP LOG ['+@DB_Name+'] WITH TRUNCATE_ONLY ' +
'DBCC SHRINKFILE( '''+@DB_LogFileName+''', 2)'
)
GO