I have been sloppy with my posts lately, and got a question how to fix the ‘Allocation Unit Size’ problemt if you are installing a SQL Server. My last post on this topic only described how to detect, but not fix the problem. To detect an invalid ‘Allocation Unit Size’ for a disk on a SQL Server (or other server for that matter), you use several methods:
My powershell function GetDiskAllocUnitSizeKB, described here.
DOS command: “fsutil fsinfo ntfsinfo <drive:>“. This command is shown in screenshot below with the specific field of interest in “Bytes Per Cluster“.
The same information can also be found by right-clicking on the disk in Windows Explorer and select “Format…”. There you have the current “Allocation Unit Size” as the same number retrieved from fsutil command.
If you want to reformat the disk with a different block size, you choose another value in the “Allocation Unit Size” combo-box and Quick format the disk (OBS! will erase the complete disk). You can also use the command-line to format the disk. For my case above, I should use the following command to format my E: drive with 64K block size.
format E: /FS:NTFS /V:DATA /Q /A:64K
Now, you have a disk partition with 64K block size suitable for large SQL Server files.
Please do not test these command on you local laptop – use a virtual machine 🙂
In my last post on setting the “Maximum Server Memory” for SQL Server, I described a simple method of calculutaing this number by using the 80% rule. This post will describe a maybe better way of calculating the “Max memory”.
During the testing an development of this new version of CalculateSQLMaximumMemory function, I discovered error in my original “max memory” post. These errors are corrected and a new table added.
This new method is better for server with less than 32GB memory.
function CalculateSQLMaximumMemory([int]$memtotal = 0)
if ($memtotal -eq 0)
$tmp = Get-WMIObject -class Win32_PhysicalMemory `
| Measure-Object -Property capacity -Sum
$memtotal = $tmp.Sum / 1MB;
if ($memtotal -lt 3072)
Write-Host "Not recommended with less than 3GB memory";
$os_mem = 2048 ;
$tot = 4096 ;
$inc_mb = 4096 ;
while ($tot -lt $memtotal)
$tot += $inc_mb ;
$os_mem += 1024 ;
if ($tot -eq 16384)
$inc_mb = 8192;
$sql_mem = $memtotal - $os_mem ;
return $sql_mem ;
$mem = CalculateSQLMaximumMemory ;
Write-Host "My local max memory: " $mem ;
$mem = CalculateSQLMaximumMemory 4096 ;
Write-Host " 4GB = SQL: " $mem ;
$mem = CalculateSQLMaximumMemory 8192 ;
Write-Host " 8GB = SQL: " $mem ;
$mem = CalculateSQLMaximumMemory 16384 ;
Write-Host " 16GB = SQL: " $mem ;
$mem = CalculateSQLMaximumMemory 32768;
Write-Host " 32GB = SQL: " $mem ;
$mem = CalculateSQLMaximumMemory 65536;
Write-Host " 64GB = SQL: " $mem ;
In the post install script, you will something like:
$maxmemory = CalculateSQLMaximumMemory ;
SetSQLInstanceMemory "." $maxmemory ;
EDIT: see version2 of the GetSQLMaxMemory function. added “$sql_mem -= ($sql_mem % 1024)” before returning from GetSQLMaxMemory. This is done to truncate to nearest MB.
When installing SQL Server, you perform a set of pre- and post installation tasks. One of these tasks is to set the “Maximum Server Memory” for each instance on your new server. There are a few different “Rules of Thumbs” described in this post. I will choose the simplest version for my first version of these powershell functions where I choose to set max Memory to 80% of total physical memory on the server.
The powershell function GetSQLMaxMemory will find the total physical memory. It is not recommended to install SQL Server on servers with less than 2GB of memory. Therefore, this function will return NULL of this is true.
$mem = Get-WMIObject -class Win32_PhysicalMemory `
| Measure-Object -Property capacity -Sum
$memtotal = ($mem.Sum / 1MB);
$min_os_mem = 2048 ;
if ($memtotal -le $min_os_mem)
$sql_mem = $memtotal * 0.8 ;
$sql_mem -= ($sql_mem % 1024) ;
return $sql_mem ;
After you have used the GetSQLMaxMemory function or calculated this manually, you can use the following Powershell function SetSQLInstanceMemory to set the minimum and maximum server memory for gievn instance.
function SetSQLInstanceMemory (
[string]$SQLInstanceName = ".",
[int]$maxMem = $null,
[int]$minMem = $null)
$srv = new-object Microsoft.SQLServer.Management.Smo.Server($SQLInstanceName)
$srv.ConnectionContext.LoginSecure = $true
$srv.Configuration.MaxServerMemory.ConfigValue = $maxMem
$srv.Configuration.MinServerMemory.ConfigValue = $minMem
Have you ever received hazzel from the security guy when you store passwords in installation and/or configiguration files. Been there, done that 🙂
But Powershell provide a feature to avoid this problem. The follwing code lines will retrieve a password from the user as crypted string (first line). The following two lines will convert the crypted password to a readable string than can be passed to setup programs. The last line (number 4), will print the password.
$password = Read-Host -Prompt "Enter default password" -AsSecureString
$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($password)
$psw = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)
I will most likely use this in my semi-automatic scripts when I shall install SQL Server, and pass the $psw to the setup.exe program as a command-line parameter as follow:
/SAPWD=$psw /SQLSVCPASSWORD=$psw /AGTSVCPASSWORD=$psw /ISSVCPASSWORD=$psw
When you are scripting SQL Server installtion, you can use powershell to perform pre-installation tasks and checks. One of these pre-installation tasks are to prepare the disk volumes on the server. For a SQL Server, the ‘Allocation Unit Size’ should be as large as possible. For most Windows Server version this is 64K. A SQL Server should split data, log, tempdb and backup into different LUN/disks, and these should be formatted with file system NTFS and the large ‘Allocation Unit Size’ at 64K.
My current disk/letter for a standard SQL Server installation is:
- OS + SQL Binaries + system databses = C
- Data disk = I
- Log disk = J
- Tempdb disk = K
- Backup disk = M
The function below returns the ‘Allocation Unit Size’ for a given drive/disk:
function GetDiskAllocUnitSizeKB([char]$drive = $null)
$wql = "SELECT BlockSize FROM Win32_Volume " + `
"WHERE FileSystem='NTFS' and DriveLetter = '" + $drive + ":'"
$BytesPerCluster = Get-WmiObject -Query $wql -ComputerName '.' `
| Select-Object BlockSize
return $BytesPerCluster.BlockSize / 1024;
This function can be used to check and determine of a disk satisfy your requirements for SQL Server. My verification script should look like following for my data og log disk. Firstly, get the ‘allocation unit size’ for the disk. The next check will determine if the retrieved value is null or not equal to 64K. If one of these expressions hits TRUE, the script will return.
$dataSize = GetDiskAllocUnitSize 'I';
if ($dataSize -eq $null -or $dataSize -ne 64)
Write-Host "Data disk 'I' should have allocation unit size of 64K"
$logSize = GetDiskAllocUnitSize 'J';
if ($logSize -eq $null -or $logSize -ne 64)
Write-Host "Log disk 'J' should have allocation unit size of 64K"