Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is a great topic, and really, every DBA or admin should blog this and be sure they understand the issue.

I saw a posting from someone that said this: they kept running low on disk space where the transaction log was kept. They would shrink the log, and they had 3 log backups per day, but the log kept growing, and this was an issue. What can they do?

Let’s examine what happens: first, assume you have a trans‌‌action log that gets 2 transactions an hour. You have enough log space for 4 transactions in your log file. You back up 3 times a day (every 8 hours). Here’s your log size in transactions:

1:00am - 2 transactions
2:00am - 4 transactions‌‌‌
3:00am - 6 transactions (log grows)
4:00am - 8 transactions (log grows) 
‌5:00am‌‌ - 10 transactions (log grows)
‌5:00am‌‌ - 12 transactions (log grows)
‌5:00am‌‌ - 14 transactions (log grows)
8:00am‌‌ - log backup with 14 transactions. Log is large enough for 14 transactions
‌9:00am - 2 transactions
10:00am - 4 transactions‌‌‌
11:00am - 6 transactions 
12:00pm - 8 transactions 
1:00pm‌‌ - 10 transactions 
‌2:00pm‌‌ - 12 transactions 
‌3:00pm‌‌ - 14 transactions 
4:00pm‌‌ - log backup with 14 transactions. Log is large enough for 14 transactions
‌4:30 - you shrink the log back to 4 transaction size
5:00pm - 2 transactions
6:00pm - 4 transactions‌‌‌
7:00pm - 6 transactions (log grows)
8:00pm - 8 transactions (log grows) 
9:00pm‌‌ - 10 transactions (log grows)
‌10:00pm‌‌ - 12 transactions (log grows)
11:00pm‌‌ - 14 transactions (log grows)
12:00am‌‌ - log backup with 14 transactions. Log is large enough for 14 transactions
‌

Repeat this every day.

Now, how does this change if we run log backups more often? Let’s say we decide to run log backups every hour. Now I get:

1:00am - 2 transactions‌‌‌, log backup runs
2:00am - 2 transactions‌‌‌, log backup runs
3:00am -2 transactions‌‌‌, log backup runs
4:00am - 2 transactions‌‌‌, log backup runs
‌5:00am‌‌ - 2 transactions‌‌‌, log backup runs
‌5:00am‌‌ - 2 transactions‌‌‌, log backup runs
‌5:00am‌‌ - 2 transactions‌‌‌, log backup runs
8:00am‌‌ - 2 transactions‌‌‌, log backup runs
‌9:00am - 2 transactions, log backup runs
10:00am - 2 transactions‌‌‌, log backup runs
11:00am - 2 transactions‌‌‌, log backup runs
12:00pm - 2 transactions‌‌‌, log backup runs
1:00pm‌‌ - 2 transactions‌‌‌, log backup runs
‌2:00pm‌‌ - 2 transactions‌‌‌, log backup runs
‌3:00pm‌‌ - 2 transactions‌‌‌, log backup runs
4:00pm‌‌ - 2 transactions‌‌‌, log backup runs
5:00pm - 2 transactions‌‌‌, log backup runs
6:00pm - 2 transactions‌‌‌, log backup runs
7:00pm - 2 transactions‌‌‌, log backup runs
8:00pm - 2 transactions‌‌‌, log backup runs
9:00pm‌‌ - 2 transactions‌‌‌, log backup runs
‌10:00pm‌‌ - 2 transactions‌‌‌, log backup runs
11:00pm‌‌ - 2 transactions‌‌‌, log backup runs
12:00am‌‌ - 2 transactions‌‌‌, log backup runs

In both scenarios, the total log transaction load across the day is the same. The total log backup size is the same across the day. However, a log backup allows me to reuse the log, so I never run out of space and get growths in the second scenario.

If you aren’t sure how things work, or want to write your own blog, I would also recommend you read this:  http://www.sqlservercentral.com/articles/Administration/64582/

‌‌