Home Forums SQL Server 7,2000 Backups Nightly Job Failing to Truncate Transaction Log RE: Nightly Job Failing to Truncate Transaction Log

  • doug.davidson (10/1/2014)


    step 3: Truncate Transaction Log

    DBCC SHRINKFILE (N'X_log' , 0, TRUNCATEONLY)

    Firstly, the comment is wrong. That doesn't truncate the log. The TruncateOnly option is only valid for data files, it's ignored when shrinking log files. All that statement does is attempt to shrink the log file to 0 MB. It won't be able to shrink anything if the active portion of the log is at the end of the file, as nothing ever moves log records around.

    Secondly, why are you shrinking the log every night? That's just a waste of resources and will result in slower transactions the next day as the log regrows.

    Shrinking the log should be a very exceptional operation, done only when some non-standard operation has grown the log well above what it needs to be for regular activity.

    http://www.sqlservercentral.com/articles/Administration/64582/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass