How to get Disk space information in SQL Server

Problem Statement:

While troubleshooting an issue in SQL Server, I wanted to know the space information of the server. Since we did not have access to the server, we have come up with a simple script as below. I am sharing the script here hoping this helps you in similar situation.


declare @svrName varchar(255)
declare @sql varchar(400)
--User Options(By default Server name)
set @svrName = Cast(SERVERPROPERTY('MachineName') as varchar(255))
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') 
	+ ' -Class Win32_Volume -Filter ''DriveType = 3'' '+
	'| select name,capacity,freespace |foreach{$''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--creating a temporary table
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #Details
EXEC xp_cmdshell @sql

--Retrieve the Capacity values in GB from PS Script Details
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #Details
where line like '[A-Z][:]%'
order by drivename

--Drop the temporary table
drop table #Details

See Also:

Using windows PowerShell to get the server disk space

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s