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

SQL Server Instance – database files location, disk, mount point, size, free space, xp_fixeddrives looklike

Goal

To obtain information about the locations were database files are, disk, mountpoint and to display the capacity, free space and space occupied on that drive by the database files

My way of achieving it

Using xp_cmdshell and powershell. The reason behind this was the fact that although xp_fixeddrives is great sometimes we need more information. This script is nothing more than an attempt to create something that can be used when we have to answer questions like

  • where my files for my databases are stored
  • the location is a mount point or not
  • what is the size of the drive where my database files are
  • how much in percentage the database files occupies from that drive

The output of the script looks like below

dltompandsizes

For me this type of information is useful when we have to show to other teams that actually the SQL Server is not the major consumer of that location or when we have to identify the disk number where the database files are in case we have to ask the storage team performance logs for it.

The script can be downloaded from here