How to truncate and shrink the transaction log in SQL Server
In SQL Server 2005
Msg 4214, Level 16, State 1, Line 1Backup 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 2008Backup log
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
ALTER DATABASE Test_db SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE (TEST_DB_log, 1)
ALTER DATABASE Test_db SET RECOVERY FULL WITH NO_WAIT
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_lsnfrom msdb..backupset where database_name = 'test_db'