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.