Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Script to Get Available and Free Disk Space for SQL Server


By:   |   Read Comments (41)   |   Related Tips: 1 | 2 | 3 | More > Database Administration

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.

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:





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





More SQL Server Solutions




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

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

 Hi,

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 'ESPAS4.ITTE.com' -Credential $credentials -Class Win32_Volume -Filter 'DriveType = 3'  select name,capacity,freespace | foreach{$_.name+'|'+$_.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

 

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

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(

SELECT

[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

)

Select

ti.MountPoint

, ti.volume

, ti.SizeMB

, ti.FreeMB

From

theInfo as ti

Group By

ti.MountPoint

, ti.volume

, ti.SizeMB

, ti.FreeMB

Order by

ti.MountPoint


Tuesday, June 02, 2015 - 9:49:20 AM - Shivendra Kumar Yadav Back To Top

Hi Jugal,

Its really awesome.

Thank you very much to post this information.

 

Shivendra.

 


Monday, March 09, 2015 - 5:52:25 PM - Michelle C Back To Top

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 Back To Top

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

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 .

Thanks

Reddy


Tuesday, July 02, 2013 - 7:33:59 AM - Pradeep Back To Top

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?

 

Thanks

Pradeep


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

Very useful script. Thanks for sharing!


Wednesday, June 05, 2013 - 8:43:03 AM - Charles Back To Top

Thanks Jugal.

 

Good one


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

 

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

Thanks Jugal.

 

Good one

 

Jayant Kumar Das

MS SQL Server DBA


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

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.

Sincerely,

Saru R

 

 


Saturday, April 06, 2013 - 11:16:57 AM - Jugal Back To Top

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 Back To Top

Jugal Could you please help here .

 

Thanks in advance .


Saturday, April 06, 2013 - 9:45:57 AM - naveen Back To Top

Sorry Blank output

Sample is Below :


(11 rows affected)
drivename


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

(0 rows affected)
drivename


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


Saturday, April 06, 2013 - 9:42:17 AM - naveen Back To Top

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

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

Hi ,

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

round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0)


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

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

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

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

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

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.

Thanks.


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

 

 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 Back To Top

This sctript will work too with Root Mounts

 

USE [master]

GO

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[GetDiskSpace]

AS

 

SET NOCOUNT ON

DECLARE @Drive varchar(100),

@SQL VARCHAR(100),

@MP binary 

 

DECLARE @Drives TABLE

(

Drive varchar(100),

Info VARCHAR(80)

)

 

DECLARE CRSR CURSOR FAST_FORWARD FOR

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

 

OPEN CRSR 

FETCH NEXT FROM CRSR INTO @Drive

WHILE @@FETCH_STATUS = 0

BEGIN

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

 

INSERT @Drives(Info)

EXEC(@SQL)

 

UPDATE @Drives

SET Drive = @Drive

WHERE Drive IS NULL

 

 

FETCH NEXT FROM CRSR INTO @Drive

END

--select * from @Drives

CLOSE CRSR

DEALLOCATE CRSR

 

-- Show the expected output

SELECT Drive,

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(

SELECT Drive,

Info

FROM @Drives

WHERE Info Is Not Null

) AS d

GROUP BY Drive

ORDER BY Drive

 

 

SELECT 

Drive, 

 CASE  

   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,

  CASE  

   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))

 ELSE 0

 END AS Pct_Free

 FROM ##TempXV1

 WHERE Drive not like '%Tmpdb%'

 

DROP TABLE ##TempXV1

 


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

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

Jugal,

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

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.

Thanks,

Jugal Shah


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

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.

Thanks,

Jugal

 


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

Hi

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

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

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

 

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

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+'|'+$
_.capacity/1048576+'%'+$_.freespace/1048576+'*'}
[NULL]

 

????


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

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

 

drivename|capacity()MB|freespace(MB)

Please advise..

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

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

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

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
BEGIN
 EXEC sp_configure 'show advanced options', 1
 RECONFIGURE;
 EXEC sp_configure 'xp_cmdshell', 1
 RECONFIGURE;
end
else
begin
 Print 'xp_cmdshell is already enabled'
end

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

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

 

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.


Learn more about SQL Server tools