0

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?

DrewB
  • 113
  • 6

2 Answers2

3

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.

David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102
2

The bottom line is:

The transaction log has to be large enough to hold an active unit of work and you need timely log backups to allow of log reuse.

Backing up the log on a periodic cycle (whether it's 1 minute, 15 minutes or 3 hours) is only going to help you if transactions are able to commit during that time frame. Active long running transactions will prevent that log space from being flagged for reuse. Also, constantly shrinking the log will have a negative performance impact due to waiting for the log to grow again. Logs do not honor Instant File Initialization.

If you're backing up your transaction log every 3 hours, you have the potential of losing up to 3 hours of transactions should you need to recover. I'd recommend re-visiting your Recovery Time Objective (RTO) and Recovery Point Objective (RPO). You might even decide to Back Up Transaction Logs Every Minute. Yes, Really.

Check out The 9 Letters That Get DBAs Fired.

Scott Hodgin - Retired
  • 24,062
  • 2
  • 29
  • 52