0

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 :

  1. I am planning to change the auto-growth setting to MBs from %.
  2. Removed Maintenance Task (Rebuild Index Task) which is created from maintenance plans option.
  3. 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:

  1. 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.

  2. 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 :

enter image description here

Ola Hallengrens Code for Index Optimization:

enter image description here

1

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

<begin>



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


<end>

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.

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> '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 -> 2
<end>

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:

  1. crash the database because of insufficient space
  2. 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

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> '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 -> 2
<end>

LSN 28 is logged for the first transaction

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> '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 -> 2
<end>

TLog has to grow

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> '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 -> 2



<end>

LSN 29 is logged for the second transaction

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> '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 -> 2
LSN 29 : Delete page content 5:444532


<end>

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:

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:

<begin>
LSN 26 : Change page content 5:444532 from 12 -> 16
LSN 27 : Change page content 5:647282 from 'Smith' -> 'Gabriel'
LSN 28 : Change page content 5:999999 from '' -> '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 -> 2
LSN 29 : Delete page content 5:444532
LSN 30 : CHECKPOINT
LSN 31 : Change page content 5:333333 from 'bad' -> 'good'
<end>

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

<begin>








LSN 30 : CHECKPOINT
LSN 31 : Change page content 5:333333 from 'bad' -> 'good'
<end>

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:

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

  1. Why Does the Transaction Log Keep Growing or Run Out of Space (DBA.SE)
  2. Switching to Simple Recovery - shrinking transaction logs (DBA.SE)
  3. SIMPLE or FULL recovery model for databases? (DBA.SE)
  4. Large number of VLF and shrinking the log (DBA.SE)
  5. How important are transaction log VLFs for performance? (DBA.SE)
  • Hi @hot2use , thank you much for the detailed explanation of what exactly is T-Log and how it works . This is really informative to a great extent . Just update on my question posted : Previously the Job runtime is close to 45 minutes and now 9 minutes. Previously the Job utilized Log drive space close to 30 GB. Now it is only 2 GB hardly. I removed the Offline Indexing part from Ola Hallengren part , it is only Online Indexing part as of now. – stack dba Jul 24 '17 at 2:45
0

This is the most frequently faced issue for almost all the DBAs where the logs grows and fills out the disk.

  • What are some reasons the transaction log grows so large?

    1.Long Active Transaction

    2.High logging transactions like Index rebuild, re-organise, Bulk Insert, Deletes etc.

    3.Any HA like Replication, Mirroring configured which holds the log and does not allow it release the log space

  • What are some ways to prevent this problem from occurring?

Log backups will help you control the log growth unless there is something that is holding up the logs from being reused. Configure the log backups to run every 15 minutes. (That's what we use)

Coming back to our question :

  • 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.

No. Index rebuild will generate logs irrespective of the way you run it because internally the t-sql will be same.

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

Scheduling log backups is the best way to control the logs (considering the Full recovery mode).Make sure it is scheduled properly and more frequently.

  • Thank you @Ramakant. I agree with you . But looks like the Ola Hallengren code for Index Optimization is conditional with page count value and fragmentation value (> or < 30%) and depending on it takes care. Where as normal Maintenance task runs through all indexes present in a database and high resource intensive occupying more disk space . This is my understanding . Please correct me if any odds in my thoughts . Yes we have every 15 mins log backup and we are on AOAG 2014 . Thanks much . – stack dba Jul 24 '17 at 2:49
  • Yes, you are on right track. Ola Hellengren solution is very well customized to address the problem rather than rebuilding everything. The 30% value can be changed and everything inside can be customized as per your need. But es again the rebuild and re-organize will be the same sql statements. Log growth during Index maintenance is an expected situation and has to be dealt with proper disk space allocation and log backups. – Ramakant Dadhichi Jul 24 '17 at 9:20
0

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.

Yes, if you currently rebuilding all indexes every time rather than rebuilding just those that have seen noticeable growth or somehow have otherwise grown significantly more fragmented. Simply because you won't be rebuilding a lot of indexes that don#'t really need it at the time.

Also the change from %age to fixed size growth will help a bit too if the logs are still getting pretty large. If the log files for a DB are currently 32Gb and more is needed a 10% growth will eat 3.2Gb even if all that is really needed at the time is a few 10s of Mb. Don't set the absolute growth value too low though as that can have performance implications of its own.

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

If you are seeing a lot of log growth fairly constantly, assuming you are using the full recovery model, then review your backup strategy and make sure you are taking log backups often enough so the space can be reused more. Though as it sounds like you are seeing huge growth just at index maintenance time I suspect this will not be something you need to do (though it is always worth revisiting backup strategies every now and then anyway, just for paranoia's sake!).

What else should I keep in mind

Another thing: don't obsess too much with keeping log files small. If after the above changes they are still regularly at 30Gb (figure plucked from the air), then let them stay at 30Gb (or a little more just for good measure) instead of shrinking them and having them need to grow again next time that much space is needed. Once you have an idea what your normal log size should be, shrink them right down as much as you can then reallocate the regular amount in one go - that way they their external fragmentation (on the filesystem) will be as low as practically possible.

  • Hi @David, the Index optimization job ran last time . Previously the Job runtime is close to 45 minutes and now 9 minutes. Previously the Job utilized Log drive space close to 30 GB. Now it is only 2 GB hardly. I removed the Offline Indexing part from Ola Hallengren part , it is only Online Indexing part as of now. I will re-test the Job with both options but any comment on disabling Offline indexing part ? – stack dba Jul 24 '17 at 2:44

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.