Script to Get Available and Free Disk Space for SQL Server

Problem

Often we face the situation where we need to check the total disk space and available disk space for both physical and LUN/Mount drives. The extended stored procedure XP_FixedDrives is unable to help us in this scenario, so we have to log into the machine to check the total and free disk space for each physical/LUN/mount drive. In this tip, I show how this can be done using PowerShell.

Solution

Before we proceed with the script, let's discuss what a LUN/Mount drive is. A LUN/Mount drive is a logical unit of a SAN created by the SAN administrator and presented to an attached server's operating system. The server is unaware of the physical makeup of the disks involved in the LUN and sees it as a single locally attached disk. That's why XP_FixedDrives is unable to query the LUN/Mount drive.

We can check the disk space by executing a PowerShell script using XP_CMDShell from SSMS. In order to do this, you need to make sure that XP_CMDSHELL is enabled on the SQL instance.

You can execute the below script to check and enable XP_Cmdshell. To enable XP_Cmdshell you must have at least the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

declare @chkCMDShell as sql_variant select @chkCMDShell = value from sys.configurations where name = 'xp_cmdshell' if @chkCMDShell = 0 begin  EXEC sp_configure 'xp_cmdshell', 1  RECONFIGURE; end else begin  Print 'xp_cmdshell is already enabled' end 

---

SQL Script to check total and free disk space of physical drive/LUN/mount drive

This is the script that can be used to get the information we are looking for. This uses PowerShell and xp_cmdshell so it can be run from a SSMS query window. You could also do this just using PowerShell, but I wanted to show how this can be done directly from SSMS.

declare @svrName varchar(255) declare @sql varchar(400) --by default it will take the current server name, we can the set the server name as well set @svrName = @@SERVERNAME set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"' --creating a temporary table CREATE TABLE #output (line varchar(255)) --inserting disk name, total space and free space value in to temporary table insert #output EXEC xp_cmdshell @sql --script to retrieve the values in MB from PS Script output select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,       (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,       (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)' from #output where line like '[A-Z][:]%' order by drivename --script to retrieve the values in GB from PS Script output select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,       (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'       ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,       (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)' from #output where line like '[A-Z][:]%' order by drivename --script to drop the temporary table drop table #output 

Sample Output

I executed the XP_Fixeddrives extended stored procedure and the above script on the same machine. You can see that XP_Fixeddrives does not show the total space or the LUN/mount drive information.

As you can see in the below image XP_FIXEDDRIVES is able to retrieve the information for the L:\ and F:\ physical disks, but is not able to retrieve the information of the LUNs or Mount Drives for example L:\UserDBData1 andF:\UserDBLog1.