How to truncate and shrink the transaction log in SQL Server

Truncating the transaction log in SQL Server breaks the chain of the logs, you will lose the log data and you will not be able to restore point in time.
The best way to free the space in the transaction log file if the database is in Full Recovery Model is to do a Transaction Log Backup without TRUNCATE_ONLY:

BACKUP LOG [Database_Name] TO DISK = N'D:\Database_Name.trn'
GO

... and after the backup, shrink the log file.

If you are completely sure that you want to truncate the transaction log without backing up the log data, and that you will lose the log data by truncating the log, here is how you can do it:

SQL Server 2005:

USE DB_Name
GO
BACKUP LOG DB_Name WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (TransactionLogFileName, 1)
GO

SQL Server 2008:

In SQL Server if you want to truncate Transaction log with:

BACKUP LOG DB_Name WITH TRUNCATE_ONLY

you will get an error with following message:


Msg 155, Level 15, State 1, Line 1
'Truncate_Only' is not a recognized BACKUP option.

This command no longer exists in SQL Server 2008. The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.

USE master
GO
ALTER DATABASE DB_Name SET RECOVERY SIMPLE WITH NO_WAIT
GO
DBCC SHRINKFILE (TransactionLogFileName, 1)
GO
ALTER DATABASE DB_Name SET RECOVERY FULL WITH NO_WAIT
GO

After switching back to full recovery model, make a full backup of the database.

SQL Server Management Studio:

Switch database to the simple recovery model:

Right-click on the database which log file you want to shrink:

Choose log file you want to shrink:

Switch database back to the full recovery model if necessary.
After switching back to full recovery model, make a full backup of the database.

If the post helped you, please share it:
Pin It

4 comments to How to truncate and shrink the transaction log in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">