ConfigMgr and SQL – NTFS allocation unit size

It’s been many years since I read that SQL databases should use an NTFS volume formatted with at 64KB file allocation unit size (block size). So long that I didn’t remember why or if it is still considered best/good practice. It appears that it is according to Microsoft and the foremost authority on SQL with ConfigMgr.

Microsoft recommends this for User Databases and the TempDB:

Steve Thompson [MVP]:

Steve explains, “The reason that SQL Server prefers 64KB NTFS cluster size, this happens to correspond to the way SQL Server allocates storage. One page = 8K, and SQL Server allocates an Extent, which is 8 pages in size. 8 pages x 8KB = 64KB/extent.”

To check the Block Size per drive/volume/partition…

From PowerShell, execute


OR from a Command Prompt


OR create a small file on the drive(s) and check the file properties.  This can easily be done by…

  • open Notepad, hold any key for about 30 seconds, then save the file
  • open a Command Prompt and type  FOR /L %I (1,1,200) DO @echo %I>> %temp%\test.file  (or similar)

Using Windows Explorer, right-click the test file and notice the Size and Size on Disk info.

From testing, the file had to be more than 500 bytes on a 4K block size volume to register any size on the disk.  On the 64K block size disk it took about 800 bytes to register.  You mileage may vary though.


In addition, if the SQL PowerShell module installed or there is some other method to query the existing SQL server, PowerShell can check if the block size on all drives where a SQL files exist or will exist if using the default file locations.


Leave a Reply


We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.