Backup and Transaction Log Maintenance

  • Good Morning Everyone,

    I hope everyone is having a nice Monday morning :crying: I came into work today and I noticed something while checking my backups and I was wondering if I could get some advice. We currently have two jobs running deleting records from two tables every hour to prune back our database, the problem I have is that the transaction logs are now the same size as the .bak's. I know this is as a result of deleting the records, and we are deleting the minimum number we can to reduce the database over a long period. My question is more to do with shrinking the logs and what would be the best way to go about it? Or is this even the best approach?

    Any help would be greatly appreciated especially considering its Monday, and nobody wants to get into this stuff on a Monday :crazy: haha

    Thanks Everyone,

    Craig

  • Craig

    Don't shrink the logs, but make sure you're backing them up regularly. Search the web for "SQL Server Transaction Log Maintenance" for more information.

    John

  • I would not recommend shrinking the transaction log as the log will probably grow again.

    How often are you performing your transaction log backups?

    You could increase the amount of transaction log backups, this would reduce the size of the transaction log backups and may also help prevent the transaction log growing

  • Please read through this - Managing Transaction Logs[/url]

    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
  • Hi John, Hi BeetleJuice,

    Thanks for getting back to me so quickly 🙂 I had a look over a few topics on your advice John and I seem to be doing things as advised. I have a backup maintenance plan taking my transaction log backups once daily (beetlejuice) and I take a full backup once weekly. Would it be advised to take them more regularly?

    Thanks again,

    Craig

  • Hi GilaMonster,

    Going to have a look now. Thank you.

  • craig.dixon (1/7/2013)


    Would it be advised to take them more regularly?

    Yes. Daily log backups mean that you can lose up to 24 hours data in the case of a disaster. If that's acceptable, consider simple recovery model and daily differential backups. If that's not acceptable, then you need log backups to match your data loss allowance.

    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
  • Hi GilaMonster,

    Thanks for the advice, I read through the article you recommended to see if I could work out a solution and I think I mite be getting somewhere. Im just wondering though, if I was to use simple recovery mode and differential backups, would the differential backups not be as large as my transaction logs? I thought that as its the two jobs I have deleting records causing the issue I would encounter the same problem?

    Thanks again for your help, I really appreciate it,

    Craig

  • Differentials only capture data changes since the last full backup. And, differentials won't help you with the logs. The best answer is to follow Gail's advice and run more frequent log backups.

    "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

  • craig.dixon (1/7/2013)


    Im just wondering though, if I was to use simple recovery mode and differential backups, would the differential backups not be as large as my transaction logs?

    Maybe, maybe not. Depends what changes you do.

    Log backups record each and every change (the operations that make the changes). Differential backups record the net result of those changes (the data after)

    The major difference would be in the size of your log files, as they would no longer need to store 24 hours of log records.

    Honestly though, the decision which should not be based on 'which is larger', but on 'which allows me to meet my SLAs'. If you have a max 2 hour data loss SAL, then your current backup strategy does not support it, neither does the switch to simple recovery and diff backups option.

    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
  • Hi again everyone,

    Thanks for all your advice today, I've had a good look at how things are working and I've decided to try a few different setups on my test environment and leave them running for a couple of weeks to see how our production environment would handle them. You've been a great help Gila, I will take your advice on board and hopefully I'll get everything working how it should.

    You've all been brilliant,

    Kindest Regards,

    Craig

  • Craig,

    The best way to go about it is to find the SLA/RTO and create a backup plan based on it. For example if your SLA requirement is for 2 hours data loss, then create a backup plan as such:

    -Weekly Full Backup

    -Daily Differential

    -Log backup every 2 hours.

    SueTons.

    Regards,
    SQLisAwe5oMe.

Viewing 12 posts - 1 through 11 (of 11 total)

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