BAK/TRN Cleanup question

  • We have a production d/b that we back up to tape every night. This process sometimes fails so I wanted my own backups

    I've set up 4 SQL Agent jobs that are now running: (I've checked the logs)

    1. Backup my production d/b nightly

    2. Backup its transaction logs hourly

    3. Remove any .BAK file older than 3 days

    4. Remove any .TRN file older than 3 days

    So I'm seeing a .BAK file and a .TRN file being created every night at 2:00 am, and disappearing 3 days later. All is good there.

    But the .TRN file is only created once a day, with the nightly BAK file at 2AM according to the date modified in Windows Explorer. Are my log backups working? The job log says they run for 3 to 7 seconds each hour, but I don't see the date modified change on an hourly basis.

    I have the TRN log backup set to Append. Is that why I don't see the Date Modified change hourly?

    Also, the size of my TRN files fluctuates wildly from day to day:

    5/22: 473 Kb

    5/23: 5.7 Gb

    5/24: 32 Mb

    Is this because my cleanup job is not actually deleting the transaction logs but just cleaning them out of older, unneeded data? Or should I be concerned about this?

    Thanks in advance

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • I would expect a single transaction log backup when using APPEND, and I'd expect the transaction log backup to grow each hour, until it was replaced by a new backup file at 2.

    _________________________________
    seth delconte
    http://sqlkeys.com

  • Okay, I'll monitor the .trn file sizes through the day. If I see it grow, then it's working.

    Should the size fluctuation each day be of any concern?

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • Yes your log backups are working, you can do a query on the MSDB tables to check that they are working as well.

    What you are doing is appending to an existing file the latest transaction log backup which is why you only see one file a day. I would personally change it to mark it with the actual date and time as well, this then will show you individual file backup sizes and also protect you against corruption, as should the single file with all logs become corrupt you wont be able to restore them.

    select

    bs.backup_set_id

    ,bs.database_name

    ,bs.type

    ,bs.backup_start_date

    ,bs.name

    ,bmf.physical_device_name

    ,bs.backup_size

    from msdb.dbo.backupset bs

    inner join msdb.dbo.backupmediafamily bmf

    on bs.media_set_id = bmf.media_set_id

    order by bs.backup_start_date

    As for the file growth sizes, that would indicate that one day you had little transactions resulting in small log backups and one day you had a few large periods of transactions resulting in a number of larger transaction log backups.

  • anthony.green (5/24/2012)


    I would personally change it to mark it with the actual date and time as well, this then will show you individual file backup sizes and also protect you against corruption, as should the single file with all logs become corrupt you wont be able to restore them.

    Anthony,

    Thank you for those very clear answers and the script as well. This raises my comfort level quite a bit.

    The daily TRN file is being datestamped in the name: (dbname)_backup_201205240206.trn so I have one TRN file per day.

    Are you saying I should timestamp each hourly append to the TRN file? How would I do that?

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • You would need to build in a dynamic date call something like the below

    declare @datestamp nvarchar(30), @cmd nvarchar(max)

    set @datestamp = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR,CURRENT_TIMESTAMP,120),'-',''),':',''),' ','')

    set @cmd = 'BACKUP LOG db1 TO DISK = C:\backups\db1'+@datestamp+'.trn'

    select @cmd

    exec (@cmd)

    Take a look at the link in my signature to Ola's scripts, he does it better than I do and has a load more options and is fully cusomisable to your requirements.

  • Will do. Thanks again!

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

Viewing 7 posts - 1 through 6 (of 6 total)

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