Script to Get Available and Free Disk Space for SQL Server

By:   |   Comments (50)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Database Administration


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.


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
   EXEC sp_configure 'xp_cmdshell', 1
   Print 'xp_cmdshell is already enabled'

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{$''|''+$_.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 #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
   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'
   (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
   (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 #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.

sql script to check total and free disk space

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, August 4, 2022 - 10:50:39 AM - Anil Aathiya Back To Top (90338)
When I try this same command for network drive type 4 it not return any output, its work if run from powershell or cmd but not working from sql agent job or task schedule.

powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 4'' | select name,capacity,freespace | foreach{$''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

Please help.

Friday, June 25, 2021 - 2:18:59 PM - Jon Back To Top (88898)
Any suggestion on how to write the PowerShell? I got here, but the format needs some cleanup.

Get-WmiObject -ComputerName <SERVERNAME> -Class Win32_Volume -Filter "DriveType = 3" | select name,capacity,freespace

How do I get the space detail trimmed down to GB?

Thursday, June 24, 2021 - 5:48:43 PM - Jugal Back To Top (88891)
The idea of this script is to run powershell command using T-SQL to retrieve the disk space information and for that XP_CmdShell is required. The other alternative is to run the powershell script directly from powershell prompt and get the storage information.

Wednesday, June 23, 2021 - 9:16:00 AM - Jon Back To Top (88886)
Our IT Security group has required the disabling of xp_cmdshell as a security precaution. Is there a way to achieve this drive storage report without using xp_cmdshell?

Monday, February 3, 2020 - 1:25:12 AM - chinta Back To Top (84112)

I would also like to know the disk no align with Lun/Mount partition for exampes:

disk6 belongs to F:/UserDBLog1

disk6 belongs to F:/UserDBLog1

disk6 belongs to F:/UserDBLog1

disk7 belongs to F:/UserDBLog2

disk8 belongs to F:/UserDBLog3

disk6 belongs to F:/UserDBLog1

disk6 belongs to F:/UserDBLog1

Monday, January 7, 2019 - 11:27:17 AM - Greg Robidoux Back To Top (78650)

Hi Trequon,

take a look at this tip.


Monday, January 7, 2019 - 10:06:58 AM - Trequon Wallace Back To Top (78649)

I need help writing or need to use a already createrd script that sends automated emails within the SQL database to alert specific users in which we set to receive an email that the disk usage space on a database is getting full over 80%.

I am using sql server 2014

Monday, July 3, 2017 - 5:04:50 AM - Debraj Dhar Back To Top (58761)

 I want a stored procedure which will inform me available memory space of a SQL Server regularly in a fixed time.


Monday, March 6, 2017 - 12:02:07 PM - Kevin Russell Back To Top (47400)

 This was just what I was looking for.




Thursday, December 17, 2015 - 5:26:14 AM - amit kumar Back To Top (40259)


can anyone help me in fetching drive info on remote servers located within domain and outside domain using powershell.

i tried, somehow during insertion in temp table, it keep hanging.

Below is what i created

$UserName = "test_domain\test_user"

$password = Get-Content "E:\temp\DiskInfo\password.txt" | ConvertTo-SecureString

$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist $UserName,$password 

Get-WmiObject Win32_LogicalDisk -ComputerName '' -Credential $credentials -Class Win32_Volume -Filter 'DriveType = 3'  select name,capacity,freespace | foreach{$'|'+$_.capacity/1048576+'%'+$_.freespace/1048576+'*'}


Seems, -Class Win32_Volume is not working for remote servers.... i tested it. can anyone help me on this.


Monday, September 28, 2015 - 1:20:47 PM - Adinarayana Back To Top (38771)


Hi Sir,


Script is awsome . it's working fine. Would like to scheduled this server for multiple servers as job on a daily basis 2 time.

And if we have a low drive space then it should be alert for every 1 min .

Could you pleaes guide me how to do that !!

Wednesday, September 23, 2015 - 11:48:43 AM - Ray Back To Top (38737)

For SQL2008R2 and later there is a nice SQL only solution to most of this problem.

sys.dm_os_Volume_Stats returns many interesting attributes for the volume a data base file resides on.  The BOL article has an example to return data for all files for all databases.  Here is a modification that returns one row per volume for any volume that has a database file for the instance.


;With theInfo as(


[Database] = db_name(f.database_id)

, [File] =

, [MountPoint] = vs.volume_mount_point

, [Volume] = vs.logical_volume_name

, [SizeMB] = vs.total_bytes / (1000 * 1000)

, [FreeMB] = vs.available_bytes / (1000 * 1000)

FROM sys.master_files AS f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) as vs




, ti.volume

, ti.SizeMB

, ti.FreeMB


theInfo as ti

Group By


, ti.volume

, ti.SizeMB

, ti.FreeMB

Order by


Tuesday, June 2, 2015 - 9:49:20 AM - Shivendra Kumar Yadav Back To Top (37359)

Hi Jugal,

Its really awesome.

Thank you very much to post this information.




Monday, March 9, 2015 - 5:52:25 PM - Michelle C Back To Top (36474)

This is very brilliant. Run this without issues - except of course on the @@servername which you have to change as suggested by Rakesh on October 01, 2014 - 1:53:14 PM (earlier post).

Wednesday, October 1, 2014 - 1:53:14 PM - Rakesh Back To Top (34803)

Please Change this part of code..It should work.

set @svrName =CONVERT(VARCHAR(128),SERVERPROPERTY('machinename') )

The powershell expects the server name..not the sql server name.


Monday, February 24, 2014 - 5:33:58 AM - Reddy Back To Top (29551)

Hi Jugal,

Thank you for the script !!

I have executed the script but i got empty output(getting only headers) and service account has administrator permission in the server.Can you please help me .. how to get the complete output .



Tuesday, July 2, 2013 - 7:33:59 AM - Pradeep Back To Top (25673)

Hi Jugal,

When I tried this script on sql instance wich is insatlled on Windows 2003 serevr, I got blank result, is this because the power shell not by default installed in Windows 2003 server?




Monday, June 10, 2013 - 7:58:42 PM - Marc Back To Top (25381)

Very useful script. Thanks for sharing!

Wednesday, June 5, 2013 - 8:43:03 AM - Charles Back To Top (25306)

Thanks Jugal.


Good one

Monday, May 13, 2013 - 12:42:29 PM - opan Back To Top (23924)


How do you run this:

CREATE PROCEDURE [dbo].[GetDiskSpace]

exec getDiskSpace ... . what do i use for the two parameters?

Sunday, May 12, 2013 - 12:30:59 PM - Jayant Das Back To Top (23908)

Thanks Jugal.


Good one


Jayant Kumar Das


Sunday, May 12, 2013 - 2:10:14 AM - Saravanan (Saru) Radhakrishnan Back To Top (23904)

Jugal - You are awesome. I have been looking for this kind of script all along.  Yes, we do have one or more LUNs mounted under a specific drive.  I have a desparate need to find the remaining disk space on these LUNs.  I am going to use your script to solve my problem.  Please note your sincere effort to come-up with this script does not go in vain, many folks including me are using it for real practical solution.  Good job man.


Saru R



Saturday, April 6, 2013 - 11:16:57 AM - Jugal Back To Top (23211)

You have to make sure that, account you are using to get the disk space should have access to windows box.

Saturday, April 6, 2013 - 10:06:11 AM - naveen Back To Top (23210)

Jugal Could you please help here .


Thanks in advance .

Saturday, April 6, 2013 - 9:45:57 AM - naveen Back To Top (23209)

Sorry Blank output

Sample is Below :

(11 rows affected)

                capacity(MB)             freespace(MB)
--------------- ------------------------ ------------------------

(0 rows affected)

                capacity(GB)             freespace(GB)
--------------- ------------------------ ------------------------

Saturday, April 6, 2013 - 9:42:17 AM - naveen Back To Top (23208)

I did not get a proper output
I Get a blang output with just the clolumn headers

Thursday, January 17, 2013 - 8:38:08 PM - Snehi Back To Top (21541)

Hi Jugal,

Thanks for your comment. Yes, it did for me. The script returned the drive name with its free space.


Previously I was testing on SQL Server 2005 where PowerShell Path was not added on the server. It took quiet a while to figure out the issue.  Thanks again! Keep posting.

Thursday, January 17, 2013 - 7:28:13 PM - Yunus Back To Top (21539)

Hi ,

I want to put this command into variable like @tspace is it possible????

      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0)

Thursday, January 17, 2013 - 10:50:57 AM - Jugal Shah Back To Top (21522)

Snehi, If you are querying the cluster SQL instance, you need to give physical node name as Server Name. Can you please execute below script by login on the remote server from powershell command prompt & let me know the result.


Get-WmiObject -class Win32_volume -filter "drivetype = 3" | select name,

 @{Name="Capacity";Expression={$_.capacity / 1GB}},@{Name="freespace";Expression

={$_.freespace / 1GB}}


Thursday, January 17, 2013 - 10:47:26 AM - Jugal Shah Back To Top (21521)

Luis,  To fix the access denied error, your SQL Server Service account should have access on the remote Server to query the disk details.

Tuesday, January 15, 2013 - 3:43:44 PM - Luis Azario Back To Top (21466)

Regarding my previous post I am pointing to a remote server, localy if Powershell is active it will work ok.

Tuesday, January 15, 2013 - 3:42:37 PM - Luis Azario Back To Top (21465)

I have the same issue, I am running the script from Sql management studio, not SSIS. Thank you

I get this error: 

Get-WmiObject : Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESS DENIED))


Tuesday, January 15, 2013 - 12:33:10 PM - Snehi Back To Top (21463)

Hi Jugal,

I tried running this query in remote server and didn't get any results. I gave the permission on service account, changed the code for @svrName and still the disk report has no result. Am I missing anything? I know it's an old post...hope you could still help.


Tuesday, November 27, 2012 - 12:22:16 PM - Vsh Back To Top (20568)


 Really excellent Jugal !!!  saved me lot of time. 


 I have 20-30 production servers where space became critical issue these days. I'm going to use SSIS package with this script to pull

 the disk size info from all the servers and load into centralized table from where I will pull out reports on the space and availability. 


 once again thank you !!!

Friday, November 2, 2012 - 7:19:12 AM - Rodney Rigdon Back To Top (20187)

This sctript will work too with Root Mounts


USE [master]


/****** Object:  StoredProcedure [dbo].[GetDiskSpace]    Script Date: 02/07/2012 09:05:00 ******/






CREATE PROCEDURE [dbo].[GetDiskSpace]




DECLARE @Drive varchar(100),


@MP binary 




Drive varchar(100),

Info VARCHAR(80)




SELECT DISTINCT LEFT(physical_name, CHARINDEX('\', SUBSTRING(physical_name, 4, LEN(physical_name)-3)) +2) FROM sys.master_files






SET @SQL = 'EXEC xp_cmdshell ''fsutil volume diskfree ' + @Drive + ''''


INSERT @Drives(Info)



UPDATE @Drives

SET Drive = @Drive






--select * from @Drives




-- Show the expected output


SUM(CASE WHEN Info LIKE 'Total # of bytes %' THEN  CAST(REPLACE(SUBSTRING(Info, 31, 42), CHAR(13), '') AS BIGINT)  ELSE CAST(0 AS BIGINT) END) AS TotalBytes,

SUM(CASE WHEN Info LIKE 'Total # of free bytes %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes

--SUM(CASE WHEN Info LIKE 'Total # of avail free bytes %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes

INTO ##TempXV1




FROM @Drives

WHERE Info Is Not Null

) AS d








   WHEN (TotalBytes * 8 / 1024.0) > 1000 THEN CAST(CAST(((TotalBytes  / 1073741824.0)) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB' 

   ELSE CAST(CAST((TotalBytes / 1048576.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB' 

 END AS Total_Size,


   WHEN (FreeBytes * 8 / 1024.0) > 1000 THEN CAST(CAST(((FreeBytes  / 1073741824.0)) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB' 

   ELSE CAST(CAST((FreeBytes / 1048576.0 ) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB' 

 END AS Free_Space,

 CASE WHEN (TotalBytes * 8 / 1024.0) > 1000 AND (FreeBytes * 8 / 1024.0) > 1000 THEN CAST((CAST(((FreeBytes  / 1073741824.0)) AS DECIMAL(18,2))/CAST(((TotalBytes  / 1073741824.0)) AS DECIMAL(18,2))) * 100.00 AS DECIMAL(18,2))

 WHEN (TotalBytes * 8 / 1024.0) < 1000 AND (FreeBytes * 8 / 1024.0) < 1000 THEN CAST((CAST(((FreeBytes  / 1073741824.0)) AS DECIMAL(18,2))/CAST(((TotalBytes  / 1073741824.0)) AS DECIMAL(18,2))) * 100.00 AS DECIMAL(18,2))

 WHEN (TotalBytes * 8 / 1024.0) > 1000 AND (FreeBytes * 8 / 1024.0) < 1000 THEN CAST((CAST(((FreeBytes  / 1073741824.0)) AS DECIMAL(18,2))/(CAST(((TotalBytes  / 1073741824.0)) AS DECIMAL(18,2))* 1000.00)) * 100.00 AS DECIMAL(18,2))


 END AS Pct_Free

 FROM ##TempXV1

 WHERE Drive not like '%Tmpdb%'




Tuesday, February 28, 2012 - 4:52:14 PM - Jugal Back To Top (16194)

Can you please put the below code in the server name and let me know the output.

set @svrName =CONVERT(VARCHAR(128),SERVERPROPERTY('machinename') ) -- instead of @@SERVERNAME

Tuesday, February 28, 2012 - 4:20:57 PM - SQL Back To Top (16193)


I tried running this query in CMS but it turned no results for 2005 server. Can you help me with this?

Thursday, August 25, 2011 - 9:55:56 AM - Jugal Shah Back To Top (14513)

Hi Maha,

To run it on all the remote servers from CMS, SQL Server Service account should have permission windows OS to query win 32. I have checked for the remote servers where service account has permission and it is working fine.


Jugal Shah

Thursday, August 25, 2011 - 9:53:49 AM - Jugal Shah Back To Top (14512)

Hi Pravin

SQL Server service account should have administration permission to retrieve the disk details. Please grant the permission to service account and it will work.




Thursday, August 25, 2011 - 5:37:41 AM - Maha Back To Top (14510)


this script works fine for me too. I tried it by executing the script for a remote server, unfortunate to get no results. can this script be modified so that it can be executed for a remote server; this would help us executing the script from a central server to all servers in the domain.

Tuesday, August 23, 2011 - 10:15:39 PM - Praveen Nakka Back To Top (14472)

Hi Jugal,

I have Local Admin Acess upon the windows server and sysadmin on the sql level.

The windows version is 2003 x64 standard edition and sql is 2005 x64 standard..

Im running this query in Master DB..

Kind Regards,

Praveen Nakka

Tuesday, August 23, 2011 - 11:52:07 AM - Jugal Back To Top (14467)

Hi Pravin, Can you give more information? SQL Server Version, OS, SQL Service Account Permission on OS, your permission and more...


Tuesday, August 23, 2011 - 11:49:51 AM - Jugal Back To Top (14466)


Hi Ihor,

You are passing the CMPSQLC01\CMPSQLC01 (Computer Name\Instance Name), Please pass the machine name only. You can get the machine name using below query.

Select CONVERT(VARCHAR(128),SERVERPROPERTY('machinename') )


Tuesday, August 23, 2011 - 9:51:43 AM - Ihor Kinal Back To Top (14461)

The powershell objects with the following:

Get-WmiObject : Invalid namespace
At line:1 char:14
+ Get-WmiObject  <<<< -ComputerName 'CMPSQLC01\CMPSQLC01' -Class Win32_Volume -
Filter 'DriveType = 3' | select name,capacity,freespace | foreach{$'|'+$



Tuesday, August 23, 2011 - 3:14:36 AM - Praveen Nakka Back To Top (14459)

Hi, when im executing the given code, I do not get any results and the result is shown as



Please advise..

Friday, July 29, 2011 - 9:36:46 AM - Johnny Back To Top (14289)

I put in the effort of pre-poulating a table with the disk space available for each drive on each individual server. The values don't change (unless a drive is lost and then I have serious problems), so I don't need to enable xp_cmdshell and just use xp_fixeddrives to populate the free space.

Thursday, July 28, 2011 - 4:40:56 AM - Ilya Back To Top (14279)

Thanks! It's very useful! One question: How can I add column of volume label name. BTW, thank u Tim G for named instance change.

Wednesday, July 27, 2011 - 9:39:57 AM - Tim G Back To Top (14272)

On one of my servers, I did not have xp_cmdshell enabled nor the advanced options shown.  I had to run

declare @chkCMDShell as sql_variant
select @chkCMDShell = value from sys.configurations where name = 'xp_cmdshell'
if @chkCMDShell = 0
 EXEC sp_configure 'show advanced options', 1
 EXEC sp_configure 'xp_cmdshell', 1
 Print 'xp_cmdshell is already enabled'

to turn on the option to see the xp_cmdshell and then enable it.

 I will be putting this script on a number of my servers. Thanks for sharing this valuable tip.



Wednesday, July 27, 2011 - 9:36:27 AM - Tripp Southern Back To Top (14271)

Thanks for a very useful script.  I've long been a heavy user of xp_fixeddrives but this is much better.  Question though, is there any reason you used a temp table instead of a table variable?  I've already changed the script to use a table variable on my end, so I know it works.  But I've only tested it on my own, SQL 2008 R2 Standard Edition (CU#7) sandbox instance running on my laptop. 

Wednesday, July 27, 2011 - 8:53:27 AM - Tim G Back To Top (14270)


When running on my server, which only has named instances, the set @srvname was resolved to server\instance.

The call to WMI expects only a servername, not a server\instance name.

I changed the code to read:

set @svrName =CONVERT(VARCHAR(128),SERVERPROPERTY('machinename') )-- @@SERVERNAME

and now it works for me.

get free sql tips
agree to terms