2 Added links to additional resources
source | link
1
source | link

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