Sql-server – How to troubleshoot large transaction log file size

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn' 
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.

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