SQL Server Instance Information – physical location, volume name, disk name and number, disk information


Obtaining information about the SQL Server physical location where the databases are and displaying for those locations the volume name, disk name and disk information

My way of achieving it

Using SSMS, xp_cmdshell and DISKPART.

In order to not use detail keyword in the script when constructing the xp_cmdshell and diskpart command I decided to us the varbinary representation of it in the script.
SELECT CONVERT(VARBINARY(MAX), ‘detail’) AS [VARBINARY], ‘varbinary representation of DETAIL keyword’ AS Info
SELECT CONVERT(VARCHAR(MAX), 0x64657461696C)AS [VARCHAR], ‘varchar representation of 0x64657461696C’ AS Info

VARBINARY – 0x64657461696C – varbinary representation of DETAIL keyword
VARCHAR – detail – varchar representation of 0x64657461696C

Before any comments about the script I want to say how important is to verify the script before execution and to run it first only on a test server or virtual machine and to run it on other systems only after you create an encrypted version of it.

Sometimes when

– we have to increase a disk where SQL Server databases are, we need to provide information about that disk to other team in order for them to increase the disk

– we have to find more information about a disk from SAN team in case we have some performance issues and those issues are tracked back to I/O latency

– we want to just display the information of disk, volumes, type, size, free in only one window from inside SSMS

The idea of having something like this came to live after I was searching how to map disks and volumes in powershell and I found the script posted by Adam Conkle ( Thanks for the script ) , here, https://gallery.technet.microsoft.com/DiskPartexe-Powershell-0f7a1bab. I realized that I can use the same approach that he uses to do the same but from inside SQL Server.

The script that I am using to obtain the output below can be downloaded from the link provided at the end of this post.


The problem is that diskpart requires administrators privileges which means that the SQL Service account must be a member of local administrators group on the server in order for the script to function or the implementation of a proxy account that has administrative privileges at OS level. More than that is advisable to have the script created as a SP and encrypt it in order to be sure that no one will replace those keywords, detail disk or detail volume with other ones like delete which can compromise or in other words to destroy the server. I have tried to replace the keywords to its varbinary representation because I considered the script much safer than having the script using the DETAIL keyword.

The script can be downloaded from here