Truncate_Only Vs NUL

How to truncate and shrink the transaction log in SQL Server

 In SQL Server 2005

Backup log with truncate_only    -- It will truncate the log without taking backup of transaction log. This command can be run against full or bulk-recovery model, SQL throws out inactive log records for that database and puts database in simple recovery model and if you run the backup log against this database it will throw the below error:

Msg 4214, Level 16, State 1, Line 1

Backup LOG cannot be performed because there is no current database backup.

The above error message indicates the log chain has been broken and the log has been truncated.

In SQL Server 2008

Backup log with truncate_only   

It throws the error 'TRUNCATE_ONLY' is not a recognized backup option.

As per the books online we should switch the database to simple recovery model, shrink the file and switch it back to full recovery model. It is the replacement for truncate_only option in SQL Server 2008 onwards

  USE TEST_DB

 GO

 ALTER DATABASE Test_db SET RECOVERY SIMPLE WITH NO_WAIT

 GO

 DBCC SHRINKFILE (TEST_DB_log, 1)

 GO

 ALTER DATABASE Test_db SET RECOVERY FULL WITH NO_WAIT

 GO

About NUL

NUL is a device, It is similar to \dev\nul in Unix environment, whatever you writes to nul it will get erased. We should not use this option frequently, if our transaction log grown out of range and we don’t have any option to stop it in such scenarios you can use this option.

When you issue a command like BACKUP DATABASE TEST_DB TO DISK='NUL' then SQL Server forms inactive log records into a transaction log file and writes into a nul device which means the operating system will erase whatever written to nul device and you will see a message showing backup log successfully processed, and it wont switch your database to SIMPLE recovery model, that’s a main difference between truncate_only and NUL

But you should be very careful before writing data into NUL device, because though it doesn't switch the database in SIMPLE recovery model it will break the log chain and resets LSN number

Query to find LSN Number

select database_name, type, first_lsn, last_lsn ,checkpoint_lsn ,database_backup_lsn

from msdb..backupset where database_name = 'test_db'