We have recently had an issue where the transaction log grew and maxed out the storage space. This happened during a maintenance window (Ola Hallengren index Optimize scripts are being used.) For some reason, someone in the past has set the transaction log backups to stop for 3 hours, it seems to be while the indexing jobs are running (they are set to run for 3 hours).
My organization recently migrated to sql server 2016 from 2008r2. The person who worked on the migration changed the window of not doing transaction logs to be 30 min shorter, so they now start at 5:30am. I think he believed they were being stopped for the full backup. But I believe they were stopped to let the index maintenance run. Is that necessary in either case? It seems like stopping tlog back ups would be doing more harm than helping.
The sql server 2008r2 schedule was:
- 3:00am: Transaction log backups are stopped
- 3:00am: Weekly Index
Optimize Job starts (this failed after 2 hours 30 min due to log
space) - 4:00am: Full backup
- 6:00am/Changed to 5:30am: Transaction log backups start (but the log remains full)
Additional info:
My organization has recently upgraded to sql server 2016 from 2008r2 in 2008r2 we had never experience this (both are enterprise edition). Could it just be a coincidence or could the upgrade have changed something? Can any one offer advice on what to check for as to the cause? The Server admin has set the jobs back to the original schedule; no tran log back ups from 3am to 6am believing that the overlap of index maintenance and transaction log backups is causing the issue, could that be true? The more research I do on the subject of transaction logs, the more it seems like we should actually be doing the log backups during this maintenance window not shutting it off. I'm a bit confused as to why they are being stopped in the first place, the original Admin is no longer with the company. I'm even more confused as to why it seems to be an issue after migration.
Best Answer
You are absolutely correct. There is no reason to stop Transaction log backup during index maintenance window. It will only make things worse as you already experienced. I had cases where I was doing maintenance on smaller size indexes I even took transaction log backup more frequently than my regular schedule.
In case if you are rebuilding large indexes you need to make sure that you have enough storage for transaction log to grow. Even you take transaction log backup during index rebuild logs will not be truncated when you have a long and active transaction. That is not the case when you reorganize indexes because logs can be truncated in between while reorganizing a single index.
Even in the Frequently Asked Questions page of Ola Hallengren this is mentioned:
The transaction log is growing very large when I run the IndexOptimize job. What should I do?
During full backup there is no need to stop transaction log backup. Read this article by By Erik Darling.
What happens to transaction log backups during full backups?