How to truncate log file.

Once I was asked a question how to truncate log file. In a scenario where log file is growing rapidly and the disk is almost full.

We can use ‘backup log’ command with any of the options TRUNCATE_ONLY, NO_LOG this options will truncate the log file without taking the backup of log files.

BACKUP LOG dbname WITH TRUNCATE_ONLY/NO_LOG/NO_TRUNCATE

This command will help in truncating the log file size. One thing we have to make note is after running this command we need to take fresh FULL/Differential backup as the above command will break the log sequence and hence it won’t allow to take any log backup. So to avoid any issue we must take a full backup.

TRUNCATE_ONLY/ NO_LOG: (This options is no longer valid in SQL Server 2008)

NO_TRUNCATE option is used when needed to backup a corrupt database.

To get the entire list of features that are no longer valid in SQL Server 2008 please check this link.