How to truncate and
shrink the transaction log in SQL Server
In SQL Server 2005
Backup log
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
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'