Not too long ago I did a bulk operation that I ended up needing to rollback. My transaction log file for that hour was 7 GB. I found that my log file is now over half the size of my data file and I'm assuming that transaction caused that growth since nothing changed with the backup schedule (the log backup has always succeeded and the full has intermittently failed recently due to space).

Both this time and in the future, what operations should I follow up with after doing large maintenance (like index rebuilds, full table updates, etc)?

I'm realizing I may need to shrink my log file back to a more typical size (since my maintenance activity was not a normal operation), but I also hesitate about this - if I have the space (I'm about to make space), should I just leave the transaction file at that size since it did need that space at one point, and will probably again (even if not during normal transactions)?

Are there any other operations I should consider during maintenance/release windows (reduced or full shutdowns)?

For reference, I have in mind our production SQL Server 2016 server with a 2008 compatibility database; currently 30 GB in size in Full recovery mode; full backups are nightly and transaction logs are hourly.

  • If you have to run the bulk operation, or index operations again, the log is going to grow again. Why bother? May 1 '17 at 14:02
  • If it's for your weekly/monthly index maintenance, then leave the log file at whatever size it needs to complete. If it's for a truly (really truly) one-off bulk operation then you can shrink it back down after you are done. But routinely shrinking the log file for a routine operation is something that should be avoided. Comparing the log size to the data size as a percentage isn't something to be concerned about, that will vary based on your workload. May 1 '17 at 14:03

I always truncate the log file after a bulk operation. Also, typically, production log backups are set to at least once every 30 minutes. I prefer 15 minutes, but an hour is a bit long. That helps in keeping log file in check too.

  • Every time you truncate your log file for a repeated process, it will have to grow again. When log files grow, they have to zero out all the disk space they're growing into (no IFI here). This can really harm performance and slow things down. You should stop doing it and stop advising other people to do it. This is bad advice and a worst practice. May 1 '17 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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