Musings of a Data professional

Stuart Moore

Get Cluster size for all disks and volumes on a Windows machine using powershell and wmi

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.

Previous

Doing something a bit different at SQL Saturday Exeter 2014: PowerShell for the curious SQL Server DBA

Next

Mission Critical SQL Server with Allan Hirt

13 Comments

  1. 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!

  2. 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 ?

    • Avatar photo

      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?

      • 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

  3. 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
    }

  4. Can you change the block size on a volume already in use?

    • Avatar photo

      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!

  5. 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?

    • Avatar photo

      Hi,
      Is it due to ComputerNam rather than ComputerName in the Get-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 exception

  6. LS

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress & Theme by Anders Norén