This question already has an answer here:
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?