Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

TRUNCATE_ONLY is not a recognized BACKUP option

30 August,2011 by Tom Collins

Executing the code below in SQL Server 2008 will throw an error message:

 

BACKUP LOG ‘MyDatabase’ WITH TRUNCATE_ONLY

Msg 155, Level 15, State 1, Line 44
'TRUNCATE_ONLY' is not a recognized BACKUP option.

It was deprecated in SQL Server 2008.To view SQL deprecated features use the Perfmon SQL Server Deprecated Features Object  

 Some options for SQL Server 2008 are :

 1)    BACKUPLOG [DBNAME] TODISK='NUL'

This will break the  transaction log chain. Therefore a Full backup is required after the command.

2)    Switch to Simple Recovery and then switch back to Full Recovery. This will required a Full backup to restart a new  transaction log chain

 3)  DBCC SHRINKFILE(Test_Log)    . This option causes fragmentation , but does the job when it comes to reclaiming space.   You might find it doesn’t shrink – in that case check for open transactions.

4) Take regular Transaction Log backups while maintaining a small log file size

 The choice will depend on the circumstances.

Read More on Transaction Logs Management

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: Tom Collins (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 TRUNCATE_ONLY is not a recognized BACKUP option


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