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 Get-PSDrive
or 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.
Eric Viele
Thanks so much for this script. I’m not a SQL admin, but I need my servers built a particular way so our SQL team can install properly. I end up being a middle man that checks everyone else’s work, so this script made quick work for me!
Thanks again!
Stuart Moore
Hi Eric,
Glad you’ve found it useful.
Cheers
Stuart
DanV
this worked well for me too !
is there a way to show disks that are attached, but active on another node in the cluster ?
Stuart Moore
Hi Dan,
Glad that worked for you.
There isn’t a direct way to do that, but we can make use of PowerShell’s support for clusters, and loop through all the nodes like this:
$cluster = Get-Cluster 'my-cluster.contoso.com'
foreach ($node in Get-ClusterNode -Cluster $cluster){
$tmp += Get-WmiObject -Query "SELECT * FROM Win32_Volume WHERE FileSystem='NTFS'" -ComputerName $node.nodename
}
$tmp | Select-Object Label, Blocksize, Name
Does that help?
DanV
that’s it exactly !
i get:
PS C:\Scripts> foreach ($node in Get-ClusterNode -Cluster $cluster){
>> $tmp += Get-WmiObject -Query “SELECT * FROM Win32_Volume WHERE FileSystem=’NTFS'” -ComputerName $node.nodename
>> }
>> $tmp | Select-Object Label, Blocksize, Name
>>
Label Blocksize Name
—– ——— —-
OSDisk 4096 C:\
EDW_DWData 65536 E:\EDW_DWData\
EDW_Misc 65536 E:\
EDW_DWStage 65536 E:\EDW_DWStage\
EDW_Logs 65536 E:\EDW_Logs\
EDW_Temp 65536 E:\EDW_Temp\
Backup 65536 X:\
EDW_DWODS 65536 E:\EDW_DWODS\
sql-production-reporting-Logs 65536 T:\SQL_Logs\
sql-production-reporting-Data 65536 T:\
sql-production-reporting-Temp 65536 T:\SQL_Temp\
OSDisk 4096 C:\
MSdtc 4096 R:\
sql-infrastructure-dbs_Logs 65536 J:\SQL_Logs\
sql-infrastructure-dbs_Temp 65536 J:\SQL_Temp\
sql-infrastructure-dbs 65536 J:\
OSDisk 4096 C:\
Quorum 4096 Q:\
OSDisk 4096 C:\
which is exactly what I was looking for!
thanks again
David
you could get the size info even easier with:
$csvs = get-clustersharedvolume
foreach($csv in $csvs){
$partition = $csv.SharedVolumeInfo.Partition
$partition.UsedSpace
$partition.FreeSpace
$partition.Size
$partition.PercentFree
}
Stuart Moore
That works as well, but does rely on having CSVs on your cluster which is still not that common (unfortunately)
Brandon
Can you change the block size on a volume already in use?
Stuart Moore
not without a reformat.
There are 3rd party tools which claim to do it, but I wouldn’t trust them on a production box/filesystem. Too much danger from a corrupted filesystem or file coming to light too late to do anything about it!
Oscar Segura
Hello Stuart,
I have been using your script quite a while with no errors; however today I was trying to get the info of a cluster I am building with 84 disks but after some info retrieved I got this error:
Get-WmiObject : provider Failure
At line:1 char:14
+ Get-WmiObject <<<< -Query $wql -ComputerNam '.' | Select-Object Label, Blocksize, Name
+ CategoryInfo : InvalidOperation: (:) [Get-WmiObject], ManagementException
+ FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand
Any clue?
Stuart Moore
Hi,
Is it due to
ComputerNam
rather thanComputerName
in theGet-WmiObject
call?If that doesn’t help, then immediately after the error, try
$error[0] | Select *
to see if there’s any more info in the raw exceptionLS
Stuart, many thanks for the great script. I asked the server team to format the drives, I trust they do the task right, and I wanted to validate before starting my work. Your script helped me, I changed the ntfs to csvfs to get the info on the shared volumes. Thank you for all the great posts you do, we appreciate very much your work.
Stuart Moore
Thank you, great to hear it’s still helping people out.