Post reply

Truncate Log on Checkpoint

  • January 7, 2002 at 8:58 am

    #104713

    In an MS SQL 7 db set up to truncate translog on checkpoint, the

    translog continues to grow despite daily backups.

    The "SQL Server Books Online" in "CHECKPOINT (T-SQL)" it is stated that

    two conditions must be met in order to truncate the translog

    automatically after each backup (checkpoint). The first condition is

    easily identifiable. The second condition is that one of the listed

    events occur. Can anyone clarify the three options and how I ensure that

    they are set/done correctly?

  • January 7, 2002 at 9:19 am

    #421805

    Based on BOL, the two conditions where a checkpoint would be set:

    • Active portion of the log exceeds the size SQL Server could recover based on the set recovery interval
    • The database is in log truncate mode and the log reaches 70% full.

    To be in log truncate mode, one of the following must be true after a BACKUP DATABASE command has been executed:

    • BACKUP LOG executed with either NO_LOG or TRUCATE_ONLY options
    • Nonlogged operation is performed
    • ALTER DATABASE statement is executed that adds or deletes a file to the database

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • July 11, 2008 at 11:42 am

    #841737

    K. Brian,

    So based on what you've quoted from BOL would you say that those conditions when a Checkpoint occurs are not RECOVERY MODEL specific so that my using a SIMPLE model does not change when a CHECKPOINT occurs?

    Thanks

    Kindest Regards,

    Just say No to Facebook!

  • July 11, 2008 at 2:38 pm

    #841822

    William (7/11/2008)

    K. Brian,

    So based on what you've quoted from BOL would you say that those conditions when a Checkpoint occurs are not RECOVERY MODEL specific so that my using a SIMPLE model does not change when a CHECKPOINT occurs?

    Thanks

    not Brian, but in my understanding, yes. Checkpoint frequency is a property of the recovery interval and the frequency of changes to the database, not the recovery model.

    The recovery model affects what gets logged and how long those log records remain in the transaction log.

    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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply