Truncate log on checkpoint

  • Hi guys

    More of a discussion than a question.

    Is there any reason not to set truncate log on checkpoint on a database?

    thanks in advance


  • If you rely on transaction log backups then you can't use it.

    If you are in a heavy production environment then you may want more control over the truncation of the log, possibly for performance reason.

    I tend to set it in my development environment only for precisely these reasons

  • Hi David

    when you say if you rely on transaction logs, what do you mean?

    I have, like you, set it in dev.  Our live environment uses FULL recovery.  We do 30min rolling transaction log backups.  How would this effect the live environment, were we to implement it?




    Read Jonathan's response there. And ignore the strange formatting as this was written in the former forum software package and migrated.

    For your question specifically, here's the management summary:

    1. Production OLTP databases should almost never be set to truncate on checkpoint. The only advantages to this setting are:

     a) that you need not worry about maintaining (i.e. backing up) the tran log, and

     b) it may make running out of disk space less likely.

    The disadvantages are:

     a) slower performance due to the truncate process running,

     b) no point in time recoverability, and

     c) no recovery to time of failure.

    If you decide that recoverability to the last full or differential backup is "good enough" for your environment, then you are forsaking sophistication for convenience.

    Frank Kalis
    Microsoft SQL Server MVP
    My blog:[/url]

  • In a production environment it is the businesses call on its recoverability - not the DBAs, in line with the SLAs. But as the DBA you need to understand what the different options are so you can present them with pros and cons.

    In dev where I am in control - everything is set to simple.

    In UAT/QA/DR it mirrors production,

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

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