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 and F:\UserDBLog1.

Next Steps

• Create the procedure SP_CheckDiskSpace using the above script in the master database and use it whenever required
• Execute the above script against CMS servers and get the disk space of all registered servers

Jugal Shah

Biography

Jugal Shah is the founder of SQLDBPool.com, where he is writes articles on SQL Server Administration and Development. Jugal has 8+ years of extensive experience in SQL Server Database administration and development. Jugal has worked on SQL Server 2000, 2005, 2008 and 2008 R2. In his current assignment, Jugal is managing large critical databases. His expertise is in database security, performance tuning and implementing high availability solutions. He can be contacted at jugal.shah@sqldbpool.com. He has been recognized as a SQL Server MVP since 2010.

• MSSQLTips Awards: Trendsetter (25+ tips) – 2013