How to truncate and shrink the transaction log in SQL Server

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.