Question

Photo of Brandon Gamache

0

Database Transaction Log Getting Large

2

What process do you use to keep the transaction log for Rock small? Ours has gotten large and I'm not certain what the best way to shorten it is--just back up the transaction log with truncate checked? If not a process, do you have any resources or websites you could share?


Thanks!

  • Photo of Daniel Hazelbaker

    0

    Hi Brandon,

        If you have a weekly (or nightly) backup job for the Rock database in place it should be taking care of truncating the transaction log as well. If you don't have a regular backup job setup, you should really set one up. :)

    • Brandon Gamache

      Brandon Gamache

      Hey Daniel,


      I've been manually right-clicking on the database>task>back up every Friday (the windows server does a full backup daily) but that is only backing up the mdf I believe. Recovery model is set to Full. Backup type is set to Full. If I have Transaction Log selected instead of Full, I can select Truncate the transaction log, otherwise I can't select that when Full is set. Is there a better way of backing up and truncating the transaction log than using Task > Back up?

      • Daniel Hazelbaker

        Daniel Hazelbaker

        Ah, sorry I guess I had slightly incorrect information. It's been a while since I actually setup the maintenance plans. Our normal backup maintenance plan has a step for "Full" backup and then another step for backing up the transaction log. So if you are doing a manual backup you would do a full backup and then do a transaction log backup and that should then shrink the transaction log.


        Hope that helps. If you need more specifics I can try to grab some screenshots of those maintenance plan steps but I don't have easy access to them at the moment.

      • Photo of David Stevens

        0

        You may need to backup and shrink the transaction log multiple times.  There are a couple recommendations here that should help a lot.