0

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?

marked as duplicate by Aaron Bertrand sql-server Oct 15 '18 at 13:18

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • What were your expectations from switching to full and taking log backups? – Erik Darling Oct 15 '18 at 12:34
  • From what I understand (which is admittedly limited), that should truncate the log after every log backup. – DrewB Oct 15 '18 at 12:44
  • Yeah nah, the truncation is internal. Your log file size won't change, just the space used inside it will be freed up. – Erik Darling Oct 15 '18 at 12:47
  • So do I need a job to run DBCC SHRINKFILE on the log? – DrewB Oct 15 '18 at 12:49
  • If you want to shrink the log file, yes, but that's a pretty bad idea. Is the log file size causing a problem, or does it just irk you for cosmetic reasons? – Erik Darling Oct 15 '18 at 12:51
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.

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.