Having been building some new SQL Server boxes recently I wanted to check that the volumes have been formatted with the appropriate block size. (64k as per this document – http://msdn.microsoft.com/en-us/library/dd758814.aspx)
I’m lucky, and have nice storage admins who’ll give me what I ask for, but it’s always better to check rather than assume. Can be a bit tricky if you discover they’re wrong after you’ve put the data on the disks.
Most examples on the interwebs appeared to rely on
fsutil. These are great little tools, but don’t play nicely with mount points, and since we use a lot of mount points they were out.
So I came up the with this quick snippet to quickly list all volumes, be they drive, mountpoint or unattached:
$wql = "SELECT Label, Blocksize, Name FROM Win32_Volume WHERE FileSystem='NTFS'" Get-WmiObject -Query $wql -ComputerName '.' | Select-Object Label, Blocksize, Name
And now I’ve got a nice table listing everything owned by the box, with enough information to work out which volume, drive or mountpoint needs a bit of attention
Label Blocksize Name ----- --------- ---- SQL-Web-Mnt 65536 L:\ SQL-Web-Mnt-Prod 65536 L:\data1\ SQL-Acct-Mnt 65536 M:\ SQL-Acct-Mnt-Prod 65536 M:\prod\ SQL-Acct-Mnt-Rep 65536 M:\reporting\ SQL-Acct-Mnt-Other 65536 M:\data\
Out of the 60 volumes I checked across various boxes, only 1 was incorrect. But fixing it now will save some time further down the line.