Determining why a transaction log file is so large I have a SQL Server 2016 Enterprise database server that I have recently inherited.  One instance on this server has an application that uses two different databases.  One of these is the actual application database and the other is used to generate reports and dump data from the first.  The second database has several stored procedures that run at different times, including one that dumps data from a view into a CSV file for offsite storage of the data that runs every 15 minutes.

When I initially got this database, the recovery model was set to simple and a database backup was being taken nightly.  Since, I have changed the recovery model to full and changed the backup plan so that it is taking full backups twice weekly, differential backups twice daily and log backups every three hours.  However, even with this change, the transaction log for the second database is staying around 300GB.  Each transaction log backup is around 80GB.  

I have plans to dig into how the stored procedures work and hopefully simplify some the way these things run, but that is going to be a long term project.  In the short term, I need to find a way to determine if the transaction log is being truncated correctly and if there is anything I can do to reduce the size of the transaction log on this database.  I've looked at the log with the fn_dblog function hoping to see if there were old transactions being stored, but most of the Begin Time and End Time records are null.  

Other than a complete rewrite of the system (which is in the works), does anyone have any suggestions for getting a handle on this log file?
the transaction log for the second database is staying around 300GB. Each transaction log backup is around 80GB. 


That sounds reasonable.  Your log file should have enough room to accommodate the log records between any two backups without growing.  And it's better if you can afford to skip a log backup, or accommodate an unusually busy period without growing.  Growing the log file requires it to be zeroed, which takes time, and there may not be space available.

So if you normally need 80GB, having 300GB reserved sounds reasonable.

If you shrink the log file after every log backup for a few days, you can probably shrink it, but I wouldn't go below 200GB probably.  Internally the effectiveness of a log shrink operation depends on which VLFs inside the log file have active records.  See Shrinking the Transaction Log and How to Shrink the SQL Server Log for an explanation of why it can take several tries to shrink the log file down to something close to the average log backup size.