SQL Server – map drives to disk numbers

Goal

To obtain the disk number of all the drives together with drive size and free space.

My way of achieving it

Using powershell and CIM_StorageExtent.

For a long period now I tried different methods and approaches in order to map the drives in Windows to their respective disk numbers. Today starting from what Granger Godbold said here, https://rvdnieuwendijk.com/2012/05/29/powershell-function-to-get-disk-scsi-lun-number/ , I finally managed to have that information. The class that I used is CIM_StorageExtent. I started with this Get-WmiObject CIM_StorageExtent  and after I filtered the output to not show me the Memory like objects I noticed that maybe the objects there appears in a specific order and I checked my assumption

name|size|freespace
Disk #1, Partition #0|214748364800|
Disk #6, Partition #0|214748364800|
Disk #7, Partition #0|214748364800|
Disk #8, Partition #0|214748364800|
Disk #9, Partition #0|214748364800|
Disk #10, Partition #0|214748364800|
Disk #11, Partition #0|268435456000|
Disk #12, Partition #0|53684994048|
Disk #13, Partition #0|53684994048|
Disk #14, Partition #0|53684994048|
Disk #15, Partition #0|53684994048|
Disk #2, Partition #0|429498826752|
Disk #16, Partition #0|53684994048|
Disk #17, Partition #0|53684994048|
Disk #18, Partition #0|53684994048|
Disk #19, Partition #0|53684994048|
Disk #20, Partition #0|161059176448|
Disk #21, Partition #0|53684994048|
Disk #22, Partition #0|161059176448|
Disk #23, Partition #0|53684994048|
Disk #24, Partition #0|53684994048|
Disk #25, Partition #0|53684994048|
Disk #3, Partition #0|644249288704|
Disk #26, Partition #0|53684994048|
Disk #27, Partition #0|53684994048|
Disk #28, Partition #0|107372085248|
Disk #29, Partition #0|53684994048|
Disk #30, Partition #0|161059176448|
Disk #31, Partition #0|53684994048|
Disk #32, Partition #0|53684994048|
Disk #33, Partition #0|161059176448|
Disk #34, Partition #0|53684994048|
Disk #35, Partition #0|966376030208|
Disk #4, Partition #0|214748364800|
Disk #36, Partition #0|966376030208|
Disk #37, Partition #0|1234814631936|
Disk #38, Partition #0|966376030208|
Disk #39, Partition #0| 1070596096|
Disk #5, Partition #0|214748364800|
Disk #0, Partition #0|209715200000|
Disk #0, Partition #1| 90248839168|
T:\SQLData\Data01\||214480257024
T:\SQLData\Data04\||36030709760
T:\SQLData\Data05\||336353427456
T:\SQLData\Data06\||183024680960
T:\SQLData\Data07\||141081640960
T:\SQLData\Data08\||214481960960
T:\SQLData\Data09\||214639247360
T:\SQLData\Data10\||214639247360
T:\SQLData\Tempdb\Data01\||212532592640
T:\SQLData\Data02\||23798415360
T:\SQLData\Data03\||70669697024
T:\SQLData\Tempdb\Log01\||53051457536
T:\SQLData\Log01\||32302104576
X:\SQLData\Tempdb\Log01\||53051981824
U:\SQLData\Data01\||53576269824
U:\SQLData\Log01\||53576269824
Y:\SQLData\Tempdb\Data01\|51477938176
…….

 

It looks like all the drives, the ones containing “\” appears in the following order:

  • disk order , partition 0
    • disk 0, partition 0
    • disk 1, partition 0
    • disk 39, partition 0
  • disk order, partition order
    • disk 0, partition 1
    • disk 0, partition 2
    • disk 0, partition n
    • disk 1, partition 1
    • disk 1, partition n
    • …..

This is the order that I noticed when I had ONLY SIMPLE VOLUMES. Maybe the order will be differently in case we have spanned volumes.

But because usually the servers in my environment are not using spanned volumes I decided to make a script to take advantage of what I just saw. The output of the script looks like below

The script can be downloaded from here