How best to maintain SQL log file sizes

Question

I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery.

Right now, I'm taking a full backup of the databases and logs every day at 5am. Some of the log files have ballooned up to 300gb and even after taking a backup they don't reduce in size. I can get them to reduce in size by running something similar to:

BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';
DBCC ShrinkFile([db1_log], 0);

BACKUP LOG db1 TO DISK = '\\server\share\db1_log2.trn';
DBCC ShrinkFile([db1_log], 0);

BACKUP LOG db1 TO DISK = '\\server\share\db1_log3.trn';
DBCC ShrinkFile([db1_log], 0);

When I check the LSNs of the backup files I see something like:

RESTORE headeronly FROM DISK = N'\\server\share\db1_log1.trn'
FirstLSN:  15781000014686200001
SecondLSN: 15802000000665000001

RESTORE headeronly FROM DISK = N'\\server\share\db1_log2.trn'
FirstLSN:  15802000000665000001
SecondLSN: 15805000000004100001

RESTORE headeronly FROM DISK = N'\\server\share\db1_log3.trn'
FirstLSN:  15805000000004100001
SecondLSN: 15808000000004200001

I don't believe I'm breaking my log chain by shrinking the log files. Reading up on this, I do believe I'm hurting my performance because those shrunk log files have to re-grow themselves.

Questions:

1. Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions?
2. At first I was thinking I should shrink the log files after every 5:00 AM backup. After reading up on how that's bad for performance I now believe that I need to take regular log backups every couple of hours during the day. Is that correct?
3. My normal full backup of the database/logs happens every day at 5:00 AM and sometimes takes 3 hours. If I schedule the log backups to happen every hour, what will happen when the log backup collides with the 5:00 AM backup?

Answer 1

1. Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions?

The actual NTFS log file doesn't "shrink" from a transaction log backup, but VLFs (Virtual Log Files) within the transaction log are marked for reuse (because they are now backed up and persisted on media) allowing the wrap-around of transaction log use to occur. If you aren't backing up the transaction log, or not frequently enough then there will be not available VLFs and that will cause the transaction log to grow (provided that autogrowth is set) to accommodate additional transaction log entries.

2.At first I was thinking I should shrink the log files after every 5:00 AM backup. After reading up on how that's bad for performance I now believe that I need to take regular log backups every couple of hours during the day. Is that correct?

Routine and scheduled file shrinkage is not a good idea. Only when you need to reclaim much needed space should you consider a DBCC SHINKFILE. Also, when you are continuously growing your transaction log, you could be hindering other things such as the recovery of the database. With too many VLFs in the transaction log (a common problem when the transaction log is only grown by a small storage increment) the amount of time to recovery the database could be longer than desired.

3.My normal full backup of the database/logs happens every day at 5:00 AM and sometimes takes 3 hours. If I schedule the log backups to happen every hour, what will happen when the log backup collides with the 5:00 AM backup?

Nothing will happen, that is a completely legal operation. See this below graph from MSDN. Where there is a black dot, those two operations can not occur at the same time. As you can see, a database backup and a transaction log are allowed concurrently.

The takeaway here is you should be backing up your transaction log more frequently. NTFS file growth isn't the only problem you could run into by not backing up your transaction log more frequently. If you were to have storage failure and your transaction log is lost, then you can only restore to the point in time of your last transaction log backup. If the transaction log is lost, you won't be able to backup the tail of the log and restore to point-in-time of the failure. In your case, you could potentially lose 24 hours worth of data. But if you backup your transaction logs every, say, 30 minutes then your maximum data loss would be 30 minutes. In that case, if your transaction log is gone, and you have your full backup and your intact log chain you could restore to that last log backup.

TechNet documentation on Transaction Log Truncation

Answer 2

The primary issue you're dealing with is that you're backing up your logs once a day. The behavior of the engine is that log records (used space) within a log file will only be removed after a successful log backup. This space is reclaimed when a checkpoint occurs, but if your database is in Full/Bulk Logged recovery then log records will only be removed if they've been successfully backed up.

Log backups are intended to be used in conjunction with Full backups and should run at a regular interval between Full backups. This interval can be any period, though I've typically run log backups every 15 minutes. Your interval is dependent on your Recovery Point Objective (RPO) and how much data you are able to lose in the event of a recovery.

If you are performing regular log backups, you shouldn't have to perform regular file shrinks because you will be managing the log file space before it's forced to grow.

Answer 3

I got the same issue like you before. My log file always increase instead I use full Database backup every night. So here is my solution:

1. backup your current log file.

2. Set your Database to Simple Recovery

   • Becasue in Full Recovery -> Log file don't delete committed transaction, it's only re-arrange your data -> Shink file not affect much -> and vice versa for Simple Recovery.

3. Shink your log file to 1 MB or less (it's up to you)

4. Set your Database back to Full Recovery.

Hope it's help

Phong Tran