BACKUP LOG with TRUNCATE_ONLY is discontinued

05 April,2012 by Jack Vamvas

As  a follow up to an earlier posts - SQL Server - BACKUP LOG WITH NO_LOG

 As of SQL Server 2008 , BACKUP LOG with TRUNCATE_ONLY is discontinued.   I still find plenty of SQL Server maintenance scripts using BACKUP LOG with TRUNCATE_ONLY.

 In SQL SQL Server 2008 – an error message will appear - TRUNCATE_ONLY is not a recognized BACKUP option.

 In SQL Server 2005 – executing BACKUP LOG with TRUNCATE_ONLY will  cause this message to appear in the SQL Server Error Logs.

 “BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.”

To view SQL deprecated features use the Perfmon SQL Server Deprecated Features Object 

If the database is in Simple Recovery , the logs are truncated automatically. If the database is in Full Recovery , and the log backup chain must be deleted – switch to Simple Recovery

Read More on SQL Backups

The transaction log for database is full due to ACTIVE_TRANSACTION

Modify sql transaction log file size - increase or decrease - SQL ...

SQL Server - Preallocate SQL Transaction Logs for large queries

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on BACKUP LOG with TRUNCATE_ONLY is discontinued


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer