Why Does My Log Grow–SQLNewBlogger

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/

 

‌‌

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s