Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Script to Get Available and Free Disk Space for SQL Server

MSSQLTips author Jugal Shah By:   |   Read Comments (36)   |   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: 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





join MSSQLTips for free SQL Server tips     



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     



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 .

Thanks

Reddy


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?

 

Thanks

Pradeep


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

MS SQL Server DBA


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.

Sincerely,

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


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

(0 rows affected)
drivename


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

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

Thanks.


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]

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

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

Thanks,

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.

Thanks,

Jugal

 


Thursday, August 25, 2011 - 5:37:41 AM - Maha Read The Tip

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

 

????


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

 

drivename|capacity()MB|freespace(MB)

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




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.