Log file backup growth (how long's a piece of string...)

  • If your log backup file was as follows:

    2,176 KB at 10 this morning

    10,617 KB at 11 this morning

    18,615 KB at 12 midday

    would that seem like an unusual amount of growth to you? (I know approx 8 MB an hour might not seem like a lot, and it's early days yet, but I wondered what others thought.)

    It has just been switched to Full recovery model, with the log being backed up every hour. Do the figures above suggest that it needs to be backed up much more frequently than that? (The database is about 58 GB.)

    (2008 R2 Standard Edition)

  • It does depend on the amount of transactions that are happening within the tables / applications.

    I would suggest that the T-LOGs are backed up every hour or even every 2 hours. the full backup should be done once a day.

    But all of this does depend on your disaster recovery plan and system availability. A 58gb database is not large in SQL terms - but is the data ALL required ? - could you archive any old data and therefore reduce the size of the database ?

    Do you do any other maintenance ? Truncate tables / shrink tables/databases ? to return free space back to the system ?

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • ... and at 1 pm it was 31,795 KB - a fairly big jump, no?

    Thanks for the reply. The log file is backed up every hour, and the database is backed up daily. The database is fairly new and all the data is needed, but there are read-only filegroups.

    Right now there's no maintenance plan in place, which is why I'm concentrating on it now. I know that the indexes need an overhaul, but I was sidetracked by watching the growth of the log file. I do think this is the week of the month when it would have the most activity, but should I be backing up the log file more frequently than this?

    Thanks

  • What is the exact command you're using to back up the 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
  • BACKUP LOG 'databasename'

    TO DISK='E:\path\databasename_LOG_BACKUP.bak'

  • No WITH options?

    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
  • No WITH options currently.

    Thanks.

  • Maybe worth reading this..

    http://technet.microsoft.com/en-us/library/ms186865.aspx

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • Try running this:

    RESTORE HEADERONLY FROM DISK = 'd:\bu\mm.bak' ;

    But on your log backup. I'll bet you you're stacking the files.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • When I run that I get 7 rows back. Is that the sort of thing you suspected?

    Thanks

  • Yes. You're appending the log backups all to the same file. Hence each time you take a log backup, the file gets bigger by the size of the log backup that just ran.

    Not a good idea. Write your backup statement so that each time the backup runs it backs up to a new file, easiest way is to stamp the date and time into the file name.

    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
  • Beatrix Kiddo (9/16/2013)


    When I run that I get 7 rows back. Is that the sort of thing you suspected?

    Thanks

    Yep. It's as Gail says.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Brilliant, thank you. I must confess I woke up in the middle of the night and that exact thought popped into my brain. I'm just run so ragged at the moment I'm making stupid mistakes.

    Thanks very much.

  • maintain your hourly backup in different files.

    And size do depend on the transactions that is going in intermediate hour. 🙂

    and rest, the masters (like Gail,, grant Etc. )have themselves provided some great explanations. Follow Them.

  • Write your backup statement so that each time the backup runs it backs up to a new file, easiest way is to stamp the date and time into the file name.

    Me again. I'm trying to take this advice, and timestamp my backup files.

    I did a search and found some help on this site here.

    But my own version of it (below) brings back an error message.

    declare @backupfile nvarchar(2000)

    set @backupfile = N'E:\path\databasename_LOG_BACKUP_' + replace(convert(nvarchar(50), getdate(), 120), ' ',':') + N'.trn'

    BACKUP LOG [i]databasename[/i] TO DISK = @backupfile

    The error message:

    Msg 3201, Level 16, State 1, Line 6

    Cannot open backup device 'E:\path\databasename_LOG_BACKUP_2013-09-18:13:04:40.trn'. Operating system error 123(failed to retrieve text for this error. Reason: 15105).

    Msg 3013, Level 16, State 1, Line 6

    BACKUP LOG is terminating abnormally.

    Googling suggests that this is a permissions issue, but I can backup to E:\path\databasename_LOG_BACKUP etc. using the GUI, so does that mean it's not a permissions issue?

    (I also thought it might be a space issue but there is absolutely tons of space on the E drive.)

    Thanks again.

Viewing 15 posts - 1 through 15 (of 18 total)

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