How do I reduce or eliminate logging?

  • Hi, here's an example to explain : DBCC SHRINKFILE(testlog, 3)

    Now if the target size is not reached with the above command, we can go on to truncate the transaction log

    BACKUP LOG pubs WITH TRUNCATE_ONLY

    🙂

  • rinu philip (7/16/2008)


    Hi, here's an example to explain : DBCC SHRINKFILE(testlog, 3)

    Now if the target size is not reached with the above command, we can go on to truncate the transaction log

    BACKUP LOG pubs WITH TRUNCATE_ONLY

    🙂

    If you like discarding log records and breaking your recovery chain, yes.

    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
  • rinu philip (7/16/2008)


    Hi, here's an example to explain : DBCC SHRINKFILE(testlog, 3)

    Now if the target size is not reached with the above command, we can go on to truncate the transaction log

    BACKUP LOG pubs WITH TRUNCATE_ONLY

    🙂

    dont truncate the log file willy nilly. If you must truncate follow immediately by a full backup to re initialise the backup chain

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • thanks!!

  • There may be another choice here.

    SQL determines when to issue a checkpoint for that database. I've seen several instances of checkpoints being issued way too infrequently on large databases, causing large log files.

    In these instances rather than issueing a backup log with truncateonly command we sheduled an hourly CHECKPOINT (for that database)

    added benefit of this is that you can run in either simple or Full recovery mode without having to find all of your truncate log commands....

    but also look at the size of your largest clustered index - this will determine the amount of storage space used when reindexing - thismigt account for why your log grows so much....

    MVDBA

  • michael vessey (7/17/2008)


    In these instances rather than issueing a backup log with truncateonly command we sheduled an hourly CHECKPOINT (for that database)

    added benefit of this is that you can run in either simple or Full recovery mode without having to find all of your truncate log commands....

    Checkpoint only truncates the logs if you are running in Simple recovery mode. In Full recovery mode, only a backup log truncates the inactive portion of 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
  • understood - that's why it's less risk - the checkpoint command will not truncate the log in FULL recovery mode.... full mode you would have a t-log backup (hopefully) anyway so the point is irrelevant.

    as i understand it the original posting was for a database in simple mode

    - implementing truncate log commands could easily turn into a nightmare if anyone takes over and decides to put the database in full recovery mode - expecting point in time restore.

    i'd recommend trying the checkpoint otion first to see if it solves your problem - nothing ventured nothing gained, if not then you need to find out why your log is not trunctating as regularly as you want, or figure out if you have a large transaction such as re-index or BCP that is forcing the log to grow in a SINGLE tranaction - in that case shrinking the log is a futile excercise as it will only grow again, and slow the large transaction down further while it waits for NTFS to allocate disk space at 10% of file size (or whatever your settings are)

    MVDBA

Viewing 7 posts - 16 through 21 (of 21 total)

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