Changing Auto-Growth Setting to MBs vs Log Drive Huge Occupancy These are our PROD Databases current settings and total information about log file utilization.

We are using SQL Server AOAGs 2014

Auto-Growth I am planning to set 512 MB


Recently we have seen huge T-Log drive utilization (close to 70 GB) each time Index Maintenance task runs (Re-build Index Task Online and Offline).

Our Log Drive Total Capacity is 300 GB (Expanded 100 GB and now 400 GB Total)


Now I am planning for below two changes :


I am planning to change the auto-growth setting to MBs from %.
Removed Maintenance Task (Rebuild Index Task) which is created from maintenance plans option.
Now I deployed Ola Hallengrens Index Optimization (Image below) and scheduled the SQL Job to run every Sunday @1:00 AM.


My Concerns and expert suggestions is highly appreciated:


Will the above changes help my Disk utilization to be at least bit lower than before. Because expanding drive each time is becoming burden for us.
What else should I keep in mind along with this to optimize Log Drive Occupancy to be lower.


Please see current settings :

PROD Server 1 :



Ola Hallengrens Code for Index Optimization:
Basics of Transaction Log files

Transaction Log files (TLog) are like a notepad with pages that hold all the information on anything that has changed during the day. The TLog (notepad) has a beginning and an end when it is opened. The TLog (notepad) will also know where it was before based on the LSN (log sequence number) (notepad: Post-IT Index). If deletes/inserts/updates are finished they are marked as COMMITed and (can be) removed from the TLog (notepad) when a backup occurs which results in a CHECKPOINT being set in the TLog file. In the notepad analogy the old notes get removed from where they were and because your notepad is always the same size, the removed pages get replaced with fresh white empty pages; be it at the beginning or at the end for your notepad. Why? Because your notepad only has space for 100 pages. The removed notepad pages are then stored in your central storage (binder).

Following is a simplistic view of the TLog file.

TLog after database has come online

&lt;begin&gt;



LSN 22 : CHECKPOINT
LSN 23 : Delete page content 1:123456 &lt;== This is where your Post-IT index is


&lt;end&gt;


Transaction Logs are Circular

Equally important: The TLog is circular in nature (round robin), so if modifications can't be stored at the end of the TLog and there is free space at the beginning, the transaction will be stored there.

&lt;begin&gt;
LSN 26 : Change page content 5:444532 from 12 -&gt; 16
LSN 27 : Change page content 5:647282 from 'Smith' -&gt; 'Gabriel'

LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -&gt; 2
&lt;end&gt;


As you can see in above example the transactions were written to the TLog and circled around in order to log further changes.

TLog Full, Now What?

Now if the TLog is full it has the following options:


crash the database because of insufficient space
Expand if the TLog allows for growth


Let's follow on with the simplistic model of my TLog file and the situation where the TLog file has to grow. Two transactions modify data. 

Before

&lt;begin&gt;
LSN 26 : Change page content 5:444532 from 12 -&gt; 16
LSN 27 : Change page content 5:647282 from 'Smith' -&gt; 'Gabriel'

LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -&gt; 2
&lt;end&gt;


LSN 28 is logged for the first transaction

&lt;begin&gt;
LSN 26 : Change page content 5:444532 from 12 -&gt; 16
LSN 27 : Change page content 5:647282 from 'Smith' -&gt; 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -&gt; 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -&gt; 2
&lt;end&gt;


TLog has to grow

&lt;begin&gt;
LSN 26 : Change page content 5:444532 from 12 -&gt; 16
LSN 27 : Change page content 5:647282 from 'Smith' -&gt; 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -&gt; 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -&gt; 2



&lt;end&gt;


LSN 29 is logged for the second transaction

&lt;begin&gt;
LSN 26 : Change page content 5:444532 from 12 -&gt; 16
LSN 27 : Change page content 5:647282 from 'Smith' -&gt; 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -&gt; 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -&gt; 2
LSN 29 : Delete page content 5:444532


&lt;end&gt;


How Does This Relate to Your Questions?

You should have the basics now, so let's have a look at how this all relates to your questions. 

If your application is modifying data or if the maintenance task is modifying data all modifications have to be stored in the TLog file. If the TLog has to grow because there is insufficient free space in the file and if the growth settings permit, then the TLog file will grow.

Yes, But You Changed Things


  Will the above changes help my Disk utilization to be at least bit lower than before. Because expanding drive each time is becoming burden for us.


percent (%) or MB growth?

Changing the growth settings from % to MB is a good thing to do, but it doesn't eliminate the need for the database to store transactions somewhere, if the space in the TLog is all used up. 

Additional reading:  


SQLSkills | Category : Transaction Log (SQLSkills)  
The Transaction Log (SQL Server) (Microsoft Docs)  
10 most important SQL Server transaction log myths (SQLShack)  


Removing Maintenance Plans

This can reduce the need for the TLog file to grow because INDEX REORGANIZE and INDEX REBUILD are no longer performed. But, ...

Adding Ola Solution

... adding the Index Optimisation Jobs from Ola will perform Index maintenance too. So you've just replaced Index Maintenance with Index Maintenance. No real gains.

Ok, So What Do You Have to Do Now?


  What else should I keep in mind along with this to optimize Log Drive Occupancy to be lower.


Adequate TLog file sizing

Don't try and shrink the TLog file too much. It will grow again. Instead try sizing the TLog file according to your observations, allow for a little growth and monitor how full the TLog file is.

Frequent TLog Backups

If you have a look at my simplistic model, you may have noted that the TLog didn't have a lot of CHECKPOINTS logged. If the database has to handle a lot of transactions modifying data, then shortening the interval between TLog backups can help keep the TLog from growing, because a TLog backup creates a CHECKPOINT which allows the data to be written to the database. Let's add an example using my over simplistic model.

Additonal Backup

A backup is performed and logged in the TLog and during the backup a user modifies data:

&lt;begin&gt;
LSN 26 : Change page content 5:444532 from 12 -&gt; 16
LSN 27 : Change page content 5:647282 from 'Smith' -&gt; 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -&gt; 'Peter'
LSN 22 : CHECKPOINT
LSN 23 : Delete page content 5:123456
LSN 24 : Delete page content 5:345678
LSN 25 : Change page content 5:463728 from 1 -&gt; 2
LSN 29 : Delete page content 5:444532
LSN 30 : CHECKPOINT
LSN 31 : Change page content 5:333333 from 'bad' -&gt; 'good'
&lt;end&gt;


We now have everything in the database (up to LSN 30) and the SQL Server could free up the TLog to look like this:

&lt;begin&gt;








LSN 30 : CHECKPOINT
LSN 31 : Change page content 5:333333 from 'bad' -&gt; 'good'
&lt;end&gt;


Great, the TLog can now continue logging modifications at the beginning again.

Optimal Virtual Log File Sizing (advanced topic)

Having an adequate VLF size (not too big, not too small) can help keep TLogs at bay. 

Additional reading:  


Important change to VLF creation algorithm in SQL Server 2014 (SQLSkills)  
Transaction Log VLFs – too many or too few? (SQLSkills)  


VLF are like subsets of pages in my simplistic model or you could look at them as groupings of pages. Having to many pages in the group (VLF) can be good or bad depending on a couple of factors. Please read up on them in the linked articles. Basically it boils down to:   


Having long running transactions can block a lot of VLFs which in turn can result in TLog growth. 
Having too many VLFs can have a negative impact on SQL Server performance.


Additional Resources


Why Does the Transaction Log Keep Growing or Run Out of Space (DBA.SE) 
Switching to Simple Recovery - shrinking transaction logs (DBA.SE)
SIMPLE or FULL recovery model for databases? (DBA.SE)
Large number of VLF and shrinking the log (DBA.SE)
How important are transaction log VLFs for performance? (DBA.SE)