sql query to get all drive total space and free space.

Question

_{Friday, June 8, 2018 3:43 PM}

hi all,

i need a query to get all the OS drives total space and free space irrespective of data file created on the drive or not. I do not want to use xp_cmdshell as its not allowed in our environment.

Thank you

All replies (8)

_{Wednesday, July 4, 2018 3:36 AM ✅Answered | 1 vote}

hi all,

i need a query to get all the OS drives total space and free space irrespective of data file created on the drive or not. I do not want to use xp_cmdshell as its not allowed in our environment.

Thank you

Try this:

SELECT distinct(volume_mount_point), 
  total_bytes/1048576 as Size_in_MB, 
  available_bytes/1048576 as Free_in_MB,
  (select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage
FROM sys.master_files AS f CROSS APPLY 
  sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576, 
  available_bytes/1048576 order by 1

A couple of more ways are here:

http://sql-articles.com/scripts/disk-space-including-mounted-drive-check-via-sql-server/

Hope this helps!

Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

---

_{Wednesday, July 4, 2018 2:47 AM | 1 vote}

As xp_cmdshell is not allowed, maybe an CLR can perform this.

---

_{Wednesday, July 4, 2018 4:59 AM}

may be

EXEC master..xp_fixeddrives

Thanks Saravana Kumar C

---

_{Wednesday, July 4, 2018 5:03 AM}

may be

EXEC master..xp_fixeddrives

Thanks Saravana Kumar C

Looks like OP's requirement is to retrieve the total sizes also.

Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

---

_{Wednesday, July 4, 2018 5:22 AM}

Hello,

SQL Server is a Server to manage data, not for OS operations, better use a PowerShell script: List the free space of all drives for several machines

Olaf Helper

[ Blog] [ Xing] [ MVP]

---

_{Wednesday, July 4, 2018 3:27 PM | 1 vote}

You can execute this script from SSMS query window to gather disk space details:

DECLARE @svrName VARCHAR(255)
DECLARE @sql VARCHAR(400)
DECLARE @output TABLE (line VARCHAR(255))

--by default it will take the current server name, we can the SET the server name as well

SET @svrName = @@SERVERNAME 
IF CHARINDEX ('\', @svrName) > 0
    SET @svrName = SUBSTRING(@svrName, 1, CHARINDEX('\',@svrName)-1)

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+''*''}"'

--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)'
      ,CAST (((ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) AS FLOAT),0)))*100/
      (ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) AS FLOAT),0)) AS INT) AS 'freespace %'
      

FROM @output
WHERE line LIKE '[A-Z][:]%'
ORDER BY drivename

HTH,

Phil Streiff, MCDBA, MCITP, MCSA

---

_{Wednesday, July 4, 2018 3:39 PM}

You can execute this script from SSMS query window to gather disk space details:

Looks like OP's requirement is not to use xp_cmdshell :)

Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

---

_{Wednesday, July 4, 2018 3:55 PM}

Ah, yes you're right.

Thanks for catching that.

HTH,

Phil Streiff, MCDBA, MCITP, MCSA