Test-DbaDiskAllocation

Synopsis

Validates disk allocation unit sizes against SQL Server best practice recommendations.

Description

Examines all NTFS volumes on target servers to verify they are formatted with 64KB allocation units, which is the recommended cluster size for optimal SQL Server performance. When checking a single server, returns a simple true/false result. For multiple servers, returns detailed information including server name, disk details, and compliance status for each volume.

The function can automatically detect SQL Server instances and identify which disks contain database files, helping you focus on storage that directly impacts SQL Server performance. System drives are automatically excluded from best practice validation since they typically don’t require the 64KB allocation unit size.

This validation is essential during SQL Server deployment planning and storage configuration audits, as improper allocation unit sizes can significantly impact database I/O performance.

References:
https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx - “The performance question here is usually not one of correlation per the formula, but whether the cluster size has been explicitly defined at 64 KB, which is a best practice for SQL Server.”

Syntax

Test-DbaDiskAllocation
    [-ComputerName] <Object[]>
    [-NoSqlCheck]
    [[-SqlCredential] <PSCredential>]
    [[-Credential] <PSCredential>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Test-DbaDiskAllocation -ComputerName sqlserver2014a

Scans all disks on server sqlserver2014a for best practice allocation unit size.

Example: 2

PS C:\> Test-DbaDiskAllocation -ComputerName sqlserver2014 | Select-Output *

Scans all disks on server sqlserver2014a for allocation unit size and returns detailed results for each.

Example: 3

PS C:\> Test-DbaDiskAllocation -ComputerName sqlserver2014a -NoSqlCheck

Scans all disks not hosting SQL Server data or log files on server sqlserver2014a for best practice allocation unit size.

Required Parameters

-ComputerName

Specifies the target server(s) to examine for disk allocation unit compliance. Accepts multiple server names for bulk validation.
Use this to verify storage configuration across your SQL Server environment during deployment or storage audits.

Optional Parameters

-NoSqlCheck

Skips detection of SQL Server database files and examines all NTFS volumes regardless of their SQL Server usage.
Use this when you want to validate allocation units on all drives, not just those containing SQL Server data or log files.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Credential

Specifies an alternate Windows account to use when enumerating drives on the server. May require Administrator privileges. To use:
$cred = Get-Credential, then pass $cred object to the -Credential parameter.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.

-Confirm

If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.