[sql-server] How do you clear the SQL Server transaction log?

I'm not a SQL expert, and I'm reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log?

1. Take a backup of the MDB file.
2. Stop SQL services
3. Rename the log file
4. Start the service

(The system will create a new log file.)

Delete or move the renamed log file.

Below is a script to shrink the transaction log, but I’d definitely recommend backing up the transaction log before shrinking it.

If you just shrink the file you are going to lose a ton of data that may come as a life saver in case of disaster. The transaction log contains a lot of useful data that can be read using a third-party transaction log reader (it can be read manually but with extreme effort though).

The transaction log is also a must when it comes to point in time recovery, so don’t just throw it away, but make sure you back it up beforehand.

Here are several posts where people used data stored in the transaction log to accomplish recovery:

• How to view transaction logs in SQL Server 2008

• Read the log file (*.LDF) in SQL Server 2008

USE DATABASE_NAME;
GO

ALTER DATABASE DATABASE_NAME
SET RECOVERY SIMPLE;
GO
--First parameter is log file name and second is size in MB
DBCC SHRINKFILE (DATABASE_NAME_Log, 1);

ALTER DATABASE DATABASE_NAME
SET RECOVERY FULL;
GO

You may get an error that looks like this when the executing commands above

“Cannot shrink log file (log file name) because the logical log file located at the end of the file is in use“

This means that TLOG is in use. In this case try executing this several times in a row or find a way to reduce database activities.

1. Backup DB
2. Detach DB
3. Rename Log file
4. Attach DB (while attaching remove renamed .ldf (log file).Select it and remove by pressing Remove button)
5. New log file will be recreated
6. Delete Renamed Log file.

This will work but it is suggested to take backup of your database first.

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.

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.

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.

Database ? right click Properties ? file ? add another log file with a different name and set the path the same as the old log file with a different file name.

The database automatically picks up the newly created log file.

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!

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.

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.

The SQL Server transaction log needs to be properly maintained in order to prevent its unwanted growth. This means running transaction log backups often enough. By not doing that, you risk the transaction log to become full and start to grow.

Besides the answers for this question I recommend reading and understanding the transaction log common myths. These readings may help understanding the transaction log and deciding what techniques to use to "clear" it:

From 10 most important SQL Server transaction log myths:

Myth: My SQL Server is too busy. I don’t want to make SQL Server transaction log backups

One of the biggest performance intensive operations in SQL Server is an auto-grow event of the online transaction log file. By not making transaction log backups often enough, the online transaction log will become full and will have to grow. The default growth size is 10%. The busier the database is, the quicker the online transaction log will grow if transaction log backups are not created Creating a SQL Server transaction log backup doesn’t block the online transaction log, but an auto-growth event does. It can block all activity in the online transaction log

From Transaction log myths:

Myth: Regular log shrinking is a good maintenance practice

FALSE. Log growth is very expensive because the new chunk must be zeroed-out. All write activity stops on that database until zeroing is finished, and if your disk write is slow or autogrowth size is big, that pause can be huge and users will notice. That’s one reason why you want to avoid growth. If you shrink the log, it will grow again and you are just wasting disk operation on needless shrink-and-grow-again game

Use the DBCC ShrinkFile ({logicalLogName}, TRUNCATEONLY) command. If this is a test database and you are trying to save/reclaim space, this will help.

Remember though that TX logs do have a sort of minimum/steady state size that they will grow up to. Depending upon your recovery model you may not be able to shrink the log - if in FULL and you aren't issuing TX log backups the log can't be shrunk - it will grow forever. If you don't need TX log backups, switch your recovery model to Simple.

And remember, never ever under any circumstances delete the log (LDF) file! You will pretty much have instant database corruption. Cooked! Done! Lost data! If left "unrepaired" the main MDF file could become corrupt permanently.

Never ever delete the transaction log - you will lose data! Part of your data is in the TX Log (regardless of recovery model)... if you detach and "rename" the TX log file that effectively deletes part of your database.

For those that have deleted the TX Log you may want to run a few checkdb commands and fix the corruption before you lose more data.

Check out Paul Randal's blog posts on this very topic, bad advice.

Also in general do not use shrinkfile on the MDF files as it can severely fragment your data. Check out his Bad Advice section for more info ("Why you should not shrink your data files")

Check out Paul's website - he covers these very questions. Last month he walked through many of these issues in his Myth A Day series.

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".

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

Most answers here so far are assuming you do not actually need the Transaction Log file, however if your database is using the FULL recovery model, and you want to keep your backups in case you need to restore the database, then do not truncate or delete the log file the way many of these answers suggest.

Eliminating the log file (through truncating it, discarding it, erasing it, etc) will break your backup chain, and will prevent you from restoring to any point in time since your last full, differential, or transaction log backup, until the next full or differential backup is made.

From the Microsoft article onBACKUP

We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately.

To avoid that, backup your log file to disk before shrinking it. The syntax would look something like this:

BACKUP LOG MyDatabaseName 
TO DISK='C:\DatabaseBackups\MyDatabaseName_backup_2013_01_31_095212_8797154.trn'

DBCC SHRINKFILE (N'MyDatabaseName_Log', 200)

-- DON'T FORGET TO BACKUP THE DB :D (Check [here][1]) 


USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

From: DBCC SHRINKFILE (Transact-SQL)

You may want to backup first.

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);

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.

Try this:

USE DatabaseName

GO

DBCC SHRINKFILE( TransactionLogName, 1)

BACKUP LOG DatabaseName WITH TRUNCATE_ONLY

DBCC SHRINKFILE( TransactionLogName, 1)

GO 

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