SQL Server TLog Flush Size and Allocation Unit Size

It is been a while from my previous blog post, so in this blog post I would like to write about SQL Server transaction log flush size and what is the best allocation unit for transaction log disk drive.

The idea came from a discussion with one my friend – Special Thanks to Siavash Golchobian – regarding what is the best allocation unit size for Transaction log file as SQL Server holds the log records in 60KB log buffers and 112KB logpool buffers.Basically setting the allocation unit size too small will decrease the write performance due to increasing the IO operations, and setting the allocation unit size too big will cause to have lots of unused storage sector and at the end wasting lots of free spaces in our disk drive.

To come out the propose allocation unit sizing, it is better to test the SQL Server environment with the real-world workload, and monitor the SQL Server behavior towards that. To perform this task, I am going to use Sysinternals to monitor the SQL Server process.

I will run the following script to test the SQL Server behavior on my machine:-

[codesyntax lang=”tsql”]

Insert Into StressDB.dbo.StressData Default Values
Go 100000000000



Now it is time to run the ProcMon.exe tool from Sysinternals, and configure the necessary options, as the following figure:-

As you see, the ProcMon.exe shows that SQL Server flushes every 1KB to the LDF file based on the given workload. Lets try the following workload:-

[codesyntax lang=”tsql”]

Insert Into StressDB.dbo.StressData(RecordPadding) 
Select Top (2) Percent RecordPadding from StressDB.dbo.StressData;



As you see the above figure, SQL Server flushes the log buffer every 60KB to the LDF file, even though SQL Server 2016 is installed on my machine.


Due to Disk sector and SQL Server log flushing behavior, I still recommend allocation unit size either 4KB or 8KB in production environments and make sure the LDF file is manually expanded to desired file size.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles


Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788


Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan