Post reply

Transaction log backups filling up much of the space --please help

  • March 27, 2007 at 8:48 am

    #61117

    Hi

    Can anyone render their thoughts on this?

    Currently we are taking Full backups every night and Transaction log backups every 30 min. On sunday we have the DB optimization job scheduled right after the full backup and that job is running for almost 3 hrs and at that time the transaction log and the log backups are occupying much space filling up lot of disk space. Can anyone let me know how do i need to schedule my backup jobs in order to avoid this problem?

  • March 27, 2007 at 8:55 am

    #697176

    You could have your transaction log just truncated and not backed up while the optimization job is running.  Then, when it is finished, take a full backup of the database and switch back to transaction log backups.

    John

  • March 27, 2007 at 8:58 am

    #697179

    Thanks for your reply. I will try that

  • March 27, 2007 at 9:15 am

    #697189

    John has great advice. It makes sense to do optmization and integrity checks first so if there are issues, then you can get an alert and be ready to fix them and run another backup. It also is good to get the optimizations done and then get that stuff out of the log. If you have low activity, take the log backup before the full, then the full, then trash the last log backup to save space.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • March 28, 2007 at 7:12 am

    #697378

    We have trouble with large amounts of transactions caused by data loading from a mainframe. It turned out that many of the transactions were just data scrubbing and clean up. We moved those into designated transfer databases and the only transactions that occur in our production databases are the inserts, updates and deletes that are required for production operation. The transaction databases run with simple logging because if something goes wrong we do not troubleshoot we just relaod from the mainframe.

    The main point is that you may have transactions that are occuring in your production systems that do not need to be there or do not need so much logging they can be moved to other databases.

Viewing 5 posts - 1 through 4 (of 4 total)

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