Estimate transaction log space needed during backup

  • Database is 3+ TB in size, simple recovery mode (OLTP DB so all data can be recovered via alternate sources).

    Full backup, which is necessary at this point, is taking 2+ days. We had to kill the first pass because it had taken up the entire 78 GB transaction log so users couldn't run business-critical queries.

    Is there a simple formula for guesstimating the log file space required? I can always make a semi-educated guess and I do have a number in mind, but I'd rather not have to babysit this process for another 2 days only to have to kill it again.

  • A 78 GB log file for a 3 TB database is less than 3% the size of your data, so that seems far too small. You probably need 10% or more, but it depends on how large your transactions are. Try setting it at around 500 GB.

    If the backup is taking 2 days, it sounds like you don't have a fast enough disk system for your application.

  • No, I don't have a fast enough disk system. I know this already. It's one of the many things that I have to change. Even worse right now I'm running the backup over the (high bandwidth) network because I have to. It's part of why I'm on this contract.

    Regarding the trans log size, in monitoring the growth through the ETL processing, the highest utilization (outside of the backup job) is around 2%. 32 hours into the backup job started the percentage used was a little over 17%. (That's the last sampling that I have.)

Viewing 3 posts - 1 through 2 (of 2 total)

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