Learn more about SQL Server tools


Latest from MSSQLTips

Script to Get Available and Free Disk Space for SQL Server

MSSQLTips author Jugal Shah By:   |   Read Comments (40)   |   Related Tips: 1 | 2 | 3 | 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{$_.name+''|''+$_.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

Last Update: 7/27/2011

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.

View all my tips

print tip Print  
Become a paid author

Learn more about SQL Server tools

Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Monday, September 28, 2015 - 1:20:47 PM - Adinarayana Read The Tip


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 Read The Tip

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] = f.name

, [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 02, 2015 - 9:49:20 AM - Shivendra Kumar Yadav Read The Tip

Hi Jugal,

Its really awesome.

Thank you very much to post this information.




Monday, March 09, 2015 - 5:52:25 PM - Michelle C Read The Tip

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 01, 2014 - 1:53:14 PM - Rakesh Read The Tip

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 Read The Tip

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 02, 2013 - 7:33:59 AM - Pradeep Read The Tip

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 Read The Tip

Very useful script. Thanks for sharing!

Wednesday, June 05, 2013 - 8:43:03 AM - Charles Read The Tip

Thanks Jugal.


Good one

Monday, May 13, 2013 - 12:42:29 PM - opan Read The Tip


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 Read The Tip

Thanks Jugal.


Good one


Jayant Kumar Das


Sunday, May 12, 2013 - 2:10:14 AM - Saravanan (Saru) Radhakrishnan Read The Tip

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 06, 2013 - 11:16:57 AM - Jugal Read The Tip

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

Saturday, April 06, 2013 - 10:06:11 AM - naveen Read The Tip

Jugal Could you please help here .


Thanks in advance .

Saturday, April 06, 2013 - 9:45:57 AM - naveen Read The Tip

Sorry Blank output

Sample is Below :

(11 rows affected)

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

(0 rows affected)

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

Saturday, April 06, 2013 - 9:42:17 AM - naveen Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip


 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 02, 2012 - 7:19:12 AM - Rodney Rigdon Read The Tip

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 Read The Tip

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 Read The Tip


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 Read The Tip

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 Read The Tip

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 Read The Tip


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 Read The Tip

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 Read The Tip

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 Read The Tip


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 Read The Tip

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{$_.name+'|'+$



Tuesday, August 23, 2011 - 3:14:36 AM - Praveen Nakka Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip


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.

More SQL Server Solutions



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.