none
truncate transaction logs in 2014 RRS feed

  • Question

  • Hi :

    Please let me know how do I truncate the logs in 2014

    I am not able to do it with

    BACKUP LOG myDB WITH TRUNCATE_ONLY

    Thanks

    Wednesday, February 11, 2015 11:50 AM

Answers

All replies

  • Hello,

    The Option TRUNCATE_ONLY was deprecated in SQL Server 2005 and removed in 2008; see BACKUP (Transact-SQL)

    Why do you want to truncate the log? The only Option is to switch recovery mode to "simple" and back to "Full"; but that will break the log backup chain.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, February 11, 2015 12:04 PM
    Moderator
  • An alternative to switching the recovery model to simple is to backup the log to null device - note that this will also break the log backup chain.

    BACKUP LOG DBName TO DISK = 'NUL'


    Satish Kartan www.sqlfood.com

    Wednesday, February 11, 2015 12:13 PM
  • I want to free up the space occupied by the log its about 200GB..and shring the database

    I guess there is an option like.. below steps

    take to simple recovery

    BACKUP Log DBName to Disk=0..

    shrink the DB

    After this we need to take a full backup.

    Am I correct

    Wednesday, February 11, 2015 12:14 PM
  • Just do the following:

    BACKUP LOG DBName TO DISK = 'NUL'

    DBCC SHRINKFILE (TLogFileLogicalName, 8192)

    Take a full backup


    Satish Kartan www.sqlfood.com

    Wednesday, February 11, 2015 12:18 PM
  • Backup log to NUL is same thing at setting to simple and then full again. Or rather, dipping down temporarily in simple has a potential for less overhead. It depends on how smart SQL Server is. There is a possibility that SQL Server actually read the ldf file in order to then write to the NUL file. This would incur higher overhead than dipping down temporarily in simple (where I assume that SQL Server just mark each VLF as re-usable).

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, February 11, 2015 4:13 PM
    Moderator
  • I dont know why no body suggested to take Transaction log backup if database is in full recovery model. If it is in simple then manual chekpoint command would truncate logs.

    I cannot see any reason to use truncate_only on backup this is just a waste command.

    Sudhakr,

    Is this still a issue? How often you take transaction log backup. Please note in full recovery model only transaction log backup can truncate the active portion of log and make them reusable unless some transaction requires it.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, February 12, 2015 5:17 AM
    Moderator
  • I have a database that has been neglected for about 4 months (~300Mb MDF, 18.2Gb LDF) and no matter how many times I try to back the files up, the LDF refuses to release the space it's used.

    In the old days, I'd do a full backup, run a DBCC BACKUP LOG <dbname> WITH Truncate_Only, then take a new full backup and a new translog backup to re-establish the chain, but that's not available now. I've tried most of the tricks in my bag, and it's still not letting go, so the "No Reason to use" is not a good argument when used as a blanket statement. I do get that MS got tired of people using this without understanding just what it did and then complaining, but then again, SQL's always been a powerful tool that didn't bother to ask "Are You Sure?" before running along and destroying data because you told it to. 


    The nice thing about standards is that there are so many of them to choose from.

    Tuesday, January 19, 2016 9:44 PM
  • But TRUNCATE_ONLY would not do anything that dimming down to simple temporarily does. You didn't lose functionality, MS just forced you a few more keyboard presses. If the log isn't cleared by doing a log backup (possibly to NUL) or dipping down to simple temporarily, then TRUNCATE_ONLY would't either. Check the log_reuse_wait_desc column in sys.databases, it should tell you why.

    Tibor Karaszi, SQL Server MVP | <a href="http://www.karaszi.com/sqlserver/default.asp"> web</a> | <a href="http://sqlblog.com/blogs/tibor_karaszi">blog</a>

    Tuesday, January 19, 2016 10:25 PM
    Moderator
  • And in addition to Tibor's answer. If you are talking about the disk space, only fiddling with BACKUP LOG is not going to reclaim any disk space. You need DBCC SHRINKFILE for that.

    Tuesday, January 19, 2016 10:41 PM
  • This worked for us when none of the other solutions would.

    Thank:-)


    Robert A. Ober www.infohou.com

    Monday, July 22, 2019 2:14 PM