none
Message: BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. Help please!

    Question

  • Hello,

    I am using SQL server 2005. Databases are in Full Recovery mode. 

    I have a job which truncate Transaction Logs. The following is the script inside job 

    USE Database
    GO
    BACKUP LOG Database WITH TRUNCATE_ONLY
    GO
    DBCC SHRINKFILE (Databaset_log, 1)
    GO
    DBCC SHRINKFILE (Databaset, 1)

    Under Error Logs, I get following message :

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

    Full backup of database exists prior to running this job.

    Experts, please help me in understanding this message. If you need any other info please let me know.

     

    Thanks in advance!

     

    Friday, May 14, 2010 3:15 PM

Answers

  • Hello SQLNinja

    The message is simple, but you really need to understand the behavior behind the message. You should read about RECOVERY MODELS in the documentation to understand, so you can start here:

    http://msdn.microsoft.com/en-us/library/ms366344(v=SQL.90).aspx

    The message itself is just saying that the command you are using will not be available in the next version you are using, so you might want to rewrite code that uses it. The message is indicating the replacement for the command to BACKUP LOG WITH TRUNCATE_ONLY is to switch your db to SIMPLE recovery, which you can learn to do by reading the documentation I pointed you to.

    And I will ask you one more question.. why do you need to SHRINK your data file? You should carefully consider all the ramifications, and read this article:

    http://www.karaszi.com/SQLServer/info_dont_shrink.asp

    HTH

    Kalen Delaney
    www.SQLServerInternals.com

     

    • Proposed as answer by Chirag Shah Friday, May 14, 2010 3:35 PM
    • Marked as answer by MuditGupta Tuesday, May 18, 2010 6:54 PM
    Friday, May 14, 2010 3:29 PM
    Moderator

All replies

  • Did you see this KB?

    http://support.microsoft.com/kb/818202

    If you have to use your database in full recovery, you should backup your transaction logs without TRUNCATE_ONLY.

    Friday, May 14, 2010 3:25 PM
  • Hello SQLNinja

    The message is simple, but you really need to understand the behavior behind the message. You should read about RECOVERY MODELS in the documentation to understand, so you can start here:

    http://msdn.microsoft.com/en-us/library/ms366344(v=SQL.90).aspx

    The message itself is just saying that the command you are using will not be available in the next version you are using, so you might want to rewrite code that uses it. The message is indicating the replacement for the command to BACKUP LOG WITH TRUNCATE_ONLY is to switch your db to SIMPLE recovery, which you can learn to do by reading the documentation I pointed you to.

    And I will ask you one more question.. why do you need to SHRINK your data file? You should carefully consider all the ramifications, and read this article:

    http://www.karaszi.com/SQLServer/info_dont_shrink.asp

    HTH

    Kalen Delaney
    www.SQLServerInternals.com

     

    • Proposed as answer by Chirag Shah Friday, May 14, 2010 3:35 PM
    • Marked as answer by MuditGupta Tuesday, May 18, 2010 6:54 PM
    Friday, May 14, 2010 3:29 PM
    Moderator
  • There is a very nice article by Paul Randal regarding why you should not use the truncate only.

    http://www.sqlskills.com/BLOGS/PAUL/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

     

    Though it is not recommended, if you really need to truncate the log you can switch from Full-Simple-Full recovery model. But you are then taking high risk not being able to have the disaster recovery. So, take a Full backup then change the database in Simple recovery and as soon as you can you should change it into Full recovery model and take a Full backup again.

    Also, go through the following link regarding shrinkdatabase

    http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/13/629059.aspx

     

    • Proposed as answer by Chirag Shah Friday, May 14, 2010 3:36 PM
    Friday, May 14, 2010 3:34 PM
  • I am in a very typical IT situation. The guy who devised all these jobs, plans has left the job and now its upto me to take care of everything. I started with checking logs and came across this message. So now, I am trying to understand the issue. 

    I don't think I can switch it to SIMPLE mode. I'll go through links posted in the thread and will try to find a way... :-) 

    Thank you!

    Friday, May 14, 2010 3:49 PM
  • While going through the links, I came across an article: 

    http://www.mssqltips.com/tip.asp?tip=1464 which says.....

    "It means a few things, first using no_log or truncate_only invalidates any transaction log backup that is performed after this statement is run.  Therefore if you have a process in your nightly routine where you are truncating the transaction log to free up space and shrink the log any transaction log backups that you run after that will not do you any good.  So by trying to not let your transaction log size get out of control you have actually caused worst issues for yourself.

    Another thing this means is that Microsoft has gotten smarter with SQL Server 2005 and does not allow you to perform additional transaction log backups after you issue a no_log or truncate_only.  In SQL Server 2005 books online it says the following:

    We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately.

    In addition:

    This option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it.

    There is a time and place where this comes in handy, if you have a huge transaction log and you do not want to do a backup, this option allows you to free up the space and then shrink the transaction log, but it was not intended to use as a general practice to be used every day."

     

    I guess I should take Backup Log with out Truncate_Only. That may help...

    Friday, May 14, 2010 4:26 PM
  • The intention of using FULL recovery mode is to have point in Time recovery. But if we use BackUp LOG with Truncate_only then we lose the recoverability option. Isn't it? Doesn't this defeats the very purpose? 
    • Proposed as answer by Nepal Sushil Sunday, May 16, 2010 12:21 PM
    Friday, May 14, 2010 8:41 PM
  • Yes you are right. So, Truncate Only has been removed from 2008.
    Sunday, May 16, 2010 12:22 PM
  • Can I run this after fullbackup:

    alter database set recovery='Simple'

    DBCC Shrink(log,1)

    alter database set recovery='Full'

    Or something else

     


    Thanks
    Wednesday, February 01, 2012 11:23 AM
  • Can I run this after fullbackup:

    alter database set recovery='Simple'

    DBCC Shrink(log,1)

    alter database set recovery='Full'

    Or something else

     


    Thanks

    Why would you want to do that? Manage the log properly and you don't have to switch recovery models or shrink. Here's an introduction to the subject (intentially kept short): http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp
    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, February 01, 2012 11:51 AM
    Moderator