Is there any reason to stop transaction log backups during a maintenance window?

Question

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.

Answer 1

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?

Make sure that the transaction log backup job is running as it should.

Check that the transaction log has the storage that it needs. You should not shrink the transaction log file. Doing so costs resources to shrink and later to regrow the file.

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?

Answer 2

SqlWorldWide is right, but I'd take a step even further back and ask: Is defragmenting your indexes even helping anything?

There's a pretty big placebo effect for folks who rebuild indexes: they also update statistics. The updated stats give El Optimizero a better description of the data in your indexes, and may even chase a bad or inefficient query plan out of cache.

Most folks run Ola's script with the defaults. The problem there is that Microsoft published that guidance around the same time Windows Server 2003 was born. Computer hardware has come a long way since then. If your server still has a bunch of drives with rotating platters, it's time to start working on getting off that thing.

A more sensible way to run Ola's scripts, if you're on Enterprise Edition, is like this:

EXEC master.dbo.IndexOptimize @Databases = 'USER_DATABASES',
                              @FragmentationMedium = 'INDEX_REORGANIZE',
                              @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE',
                              @FragmentationLevel1 = 50,
                              @FragmentationLevel2 = 80,
                              @MinNumberOfPages= 5000,
                              @UpdateStatistics = 'ALL',
                              @OnlyModifiedStatistics = 'Y',
                              @LogToTable = 'Y';

If you're on Standard Edition, you can't Rebuild Online, so you could use this:

EXEC master.dbo.IndexOptimize @Databases = 'USER_DATABASES',
                              @FragmentationMedium = 'INDEX_REORGANIZE',
                              @FragmentationHigh = 'INDEX_REBUILD_OFFLINE,INDEX_REORGANIZE',
                              @FragmentationLevel1 = 50,
                              @FragmentationLevel2 = 80,
                              @MinNumberOfPages = 5000,
                              @UpdateStatistics = 'ALL',
                              @OnlyModifiedStatistics = 'Y',
                              @LogToTable = 'Y';

What do these buy you?

1. Only working on indexes that have a fragmentation level that could start to show for larger scans
2. Only working on indexes of a size where defragmenting them reduces the number of pages you'd have to read by a significant size
3. Taking care of your modified statistics

If you start running maintenance with those settings, and no one fusses, you can switch from running them nightly to weekly. Or even monthly.

You're still going to want to update stats regularly though, and you can do that with this command:

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
                          @FragmentationLow = NULL,
                          @FragmentationMedium = NULL,
                          @FragmentationHigh = NULL,
                          @UpdateStatistics = 'ALL',
                          @OnlyModifiedStatistics = 'Y',
                          @StatisticsSample = NULL,
                          @LogToTable = 'Y';

Index maintenance is both annoying and confusing -- I used to be pretty stupid about it. Once I got over the OCD need to keep indexes as unfragmented as possible and started concentrating on things that actually helped performance, my servers were in much better shape. I also had a lot more time for important things, like DBCC CHECKDB.

Hope this helps!