Get the allocation unit size of an NTFS partition in Server 2012

If you’re a SQL Server Pro you will know that the volumes the SQL log and datafiles are stored on should be formatted with a 64K cluster size. This is a SQL Server Best practice as stated in this link.

To find out what the allocation unit size is for a drive you would run the command below:

fsutil fsinfo ntfsinfo [your drive]

Running the same command on a test system on the E:\ drive I get the following result:

C:\>fsutil fsinfo ntfsinfo E:
NTFS Volume Serial Number :       0x0c76bbd176bbba32
NTFS Version   :                  3.1
LFS Version    :                  2.0
Number Sectors :                  0x00000000013fe7ff
Total Clusters :                  0x0000000000027fcf
Free Clusters  :                  0x0000000000027d5b
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Physical Sector :       512
Bytes Per Cluster :               65536
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000000010000
Mft Start Lcn  :                  0x000000000000c000
Mft2 Start Lcn :                  0x0000000000000001
Mft Zone Start :                  0x000000000000c000
Mft Zone End   :                  0x000000000000cca0
Resource Manager Identifier :     28DB2529-6D43-11E6-80C7-00505693BAC6

As you can see from the above, the Bytes Per Cluster is equal to 65,536 bytes or 64K.

You would have to run this command for every drive that you want to check. I found this handy piece of PowerShell code that gets just the allocation unit size for all disks in the system:

$wql = "SELECT Label, Blocksize, Name FROM Win32_Volume WHERE FileSystem='NTFS'"
Get-WmiObject -Query $wql -ComputerName '.' | Select-Object Label, Blocksize, Name

The result of which would look similar to this on a system running SQL Server:

20160830081407

The results above show which disks are configured with the default 4K allocation unit size and those configured with 64K which are the SQL disks.

References:
How To Determine Your Hard Disks Cluster Size
SQL Server Best Practices Article
Disk Partition Alignment Best Practices for SQL Server
Get Cluster size for all disks and volumes on a Windows machine using powershell and wmi

Missing Windows Installer cache files prevent SQL Server 2008 R2 Service Pack from installing

When trying to install a SQL Server 2008 & 2008 R2 Service Pack you may see the following error:

1612 The installation source for this product is not 
available. Verify that the source exists and that you can 
access it.

These issues can occur when the Windows Installer database files (.msi) or the Windows Installer patch files (.msp) are missing from the Windows Installer cache, which is located in %windir%\installer.

To resolve this issue go to KB969052 and run the FindSQLInstallsOnly.vbs from a command prompt. The output file sql_install_details.txt will contain information on the missing files and will advise where to copy these missing files to. Follow the instructions and once all the missing files have been replaced re-run the script and the output file should confirm the existance of the missing file(s), it should be similar to the below:

SQLServerXXXXXX.msi exists on the LastUsedSource path, 
no actions needed.

After you have confirmed there are no more missing files re-attempt the SQL Server Service Pack installation and this time it should be successfull. I have seen this issue a handful of times in the past and it is worth noting KB969052 so that you can respond swiftly, especially if you are running according to a tight maintenance window.