In Microsoft SQL server 2000 and 2005, as part of the ‘BACKUP LOG’ command, there was an option to truncate the log file, without storing a backup to file. In Microsoft SQL Server 2008, this option has been removed, and you can now no longer use the ‘TRUNCATE_ONLY’ option when performing a transaction log backup.
The truncate only option is used to truncate the log file. This is generally done so you can then shrink the transaction log file, and recover the disk space back to the file system.
The error message that occurs when you try the ‘TRUNCATE_ONLY’ option in Microsoft SQL 2008 is as follows:
Msg 155, Level 15, State 1, Line 1 'TRUNCATE_ONLY' is not a recognized BACKUP option.
To truncate the transaction log file in Microsoft SQL Server 2008, without making an actual transaction log backup (possibly due to free space limitations), you need to change the recovery model of the database to “Simple”, and then change it back to “Full” (or “Bulked Logged” if that’s what it was previously).
In the process of making the database recovery model “Simple”, it truncates the transaction log file, as it is not used with the simple recovery model.
This can be done manually via the Microsoft SQL Server Management Studio tool, or it can be done via SQL statements, and scripted if needed.
SQL statements to change the database model of a specific database:
ALTER DATABASE [<database_name>] SET RECOVERY SIMPLE ALTER DATABASE [<database_name>] SET RECOVERY FULL
After doing the above, you can shrink the transaction log file using the following command:
DBCC SHRINKFILE(<log_name>)
For more information on shrinking the transaction log file, please check out the “How to shrink a Microsoft SQL transaction log file” article.
