How best to maintain SQL log file sizes

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:

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

• 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?

• 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?

• 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

StackExchange Database Administrators Q#40833, answer score: 10