Need advice with backup plan and transaction log file

  • I am missing something I guess.  Here's our backup Plan

    • Full backup weekly
    • Differential backup daily
    • Transaction log backup at each hour.

    for each we use procedure created by Ola Hallengren  ( called with specific parameters for the backup type.

    Am I wrong saying that immediatelly after the full backup, the transaction log file  is useless?  So how to reduce the size of this file (actually 75 gigs and still growing)?  I thought the full backup would reduce is size.   Which command should I add after the full backup to empty the transaction log file?

    thank you





  • The plan is okay.

    If you want to shrink the log, you gotta use the dbcc shrinkfile.

    I don't think you should be shrinking the log file if its still increasing in size, you should check why its growing so much in the first place, you are going to have the same issues until you fix why its growing so much.

    you shrink the transaction log after cleaning the VLFs, these are cleaned after a transaction log backup, not after a full backup.

  • Am I wrong saying that immediatelly after the full backup, the transaction log is useless?

    Yes, that is quite wrong.

    The transaction log would still be needed to recover to a point-in-time after the full backup.  If you never need to recover to point in time / you can recover only to diff backups and still be good, then you don't need to do log backups and should stop doing them.

    Command DBCC SHRINKFILE(2, <size_in_mb>) can be used to shrink a log file, but you don't want to shrink below what you actually need in the log file.  Otherwise SQL will just grow the log again, and that is huge overhead compared to just leaving the space allocated to the log there.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • How are you calling the log backup with Ola’a code?

    Are you doing the @copy_only = ‘y’


  • on top of the other replies:

    Only a log backup marks the log file entries to be overwritten except for open transactions.

    DO NOT SHRINK LOG FILES ! ( unless you performed a one time large operation which had the log file exploding )

    Chances are your log file will need to grow again ( slow operation !  ) !

    If you have the need to have a smaller log file, elevate the frequency by which you run log backups.

    If you create a log backup once an hour, you may lose an hour worth of data in case of disaster.

    Does that meet your sla's RPO ?


    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Ant-Green wrote:

    How are you calling the log backup with Ola’a code?

    Are you doing the @copy_only = ‘y’

    Like this

    EXECUTE DatabaseBackup
    @Databases = 'BDNAME',
    @Directory = 'G:\BACKUP',
    @BackupType = 'LOG',
    @CleanupTime = 96; --Keep file for 96 hours.
  • This was removed by the editor as SPAM

  • Verify and does the log get written to a folder called LOG or LOG_COPY_ONLY.

    Also verify that @copy_only on the procedure code is set to N.

    Also verify what the log_wait_reuse_desc is in sys.databases

  • Perhaps the logs backups are configured, but not running.

  • One could argue that your plan is fatally flawed.

    It should not be a backup plan, it should be a recovery plan.

    As such, you likely would have discovered the answers to your questions because you have taken the time to go through each of the possible scenarios and validate that the recovery works, and that you can complete them properly so that the RTO and RPO' s are met.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:

Viewing 10 posts - 1 through 10 (of 10 total)

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