Get total disk size and free disk space

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Get total disk size and free disk space

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 11:32:27

[code]-- Initialize Control Mechanism
DECLARE @Drive TINYINT,
@SQL VARCHAR(100)

SET @Drive = 97

-- Setup Staging Area
DECLARE @Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)

WHILE @Drive <= 122
BEGIN
SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''

INSERT @Drives
(
Info
)
EXEC (@SQL)

UPDATE @Drives
SET Drive = CHAR(@Drive)
WHERE Drive IS NULL

SET @Drive = @Drive + 1
END

-- Show the expected output
SELECT Drive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), 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
FROM (
SELECT Drive,
Info
FROM @Drives
WHERE Info LIKE 'Total # of %'
) AS d
GROUP BY Drive
ORDER BY Drive[/code]

E 12°55'05.25"
N 56°04'39.16"

gvphubli
Yak Posting Veteran

54 Posts

Posted - 2007-12-18 : 14:56:41

there is one more article at this page
http://www10.brinkster.com/technologyyogi/newsite/Articles.htm

- = Cracky DBA = -
http://www.geocities.com/gvphubli/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-18 : 15:26:10

The problem with both of these approaches is that they do not get the information for mount points, which is now supported in SQL Server 2005. I had to create a .NET CLR function that called Performance Monitor counters to get the disk information since that's the only place where I could find the information about mount points.

I'll eventually get around to posting my solution on my blog.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 15:31:36

http://en.wikipedia.org/wiki/Volume_Mount_Point

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-18 : 18:49:39

I found some time today to get my solution on my blog: http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

RyanAustin
Yak Posting Veteran

50 Posts

Posted - 2008-03-25 : 19:03:35

The compiled dll works great for a 2005 SQL instance, but is there anything that would work for a 2000 instance??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-25 : 20:32:16

As long as you create the CLR on a 2005 server and then point it at a 2000 instance, it will be able to pull the information since I'm just getting the data from a Performance Monitor object.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

PABluesMan
Starting Member

26 Posts

Posted - 2008-03-26 : 12:00:54

Another big problem is that it requires the use of [xp_cmdshell]. I generally like to keep this disabled for security purposes, so this procedure would not work at all in those instances.

I've come up with an alternative that does not require [xp_cmdshell]. It's definitely not pretty, but it works ... sort of (some of the properties it tries to get can't be retrieved, depending on whether or not the file is local, the OS version, etc.). However, it's been working for me for some time.

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99653[/url]

Hope this helps!

I geek, therefore I am

Haywood
Posting Yak Master

221 Posts

Posted - 2008-03-26 : 12:29:53

Using the sp_OA methods...this can be saved to a file and then used by osql/sqlcmd which can in turn be used in a FOR loop from the cmdshell...


/******************************************************************************
**
**
**	Author:  G. Rayburn
**
**	Date:  11/01/2005
**
**	ToDo:	     
**
**
*******************************************************************************
**		Modification History
*******************************************************************************
**
**	Initial Creation:  11/01/2005 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/

SET NOCOUNT ON


IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
	DROP TABLE ##_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
	DROP TABLE ##_DriveInfo


DECLARE @Result INT
	, @objFSO INT
	, @Drv INT 
	, @cDrive VARCHAR(13) 
	, @Size VARCHAR(50) 
	, @Free VARCHAR(50)
	, @Label varchar(10)

CREATE TABLE ##_DriveSpace  
	(
	  DriveLetter CHAR(1) not null
	, FreeSpace VARCHAR(10) not null

	 )

CREATE TABLE ##_DriveInfo
	(
	DriveLetter CHAR(1)
	, TotalSpace bigint
	, FreeSpace bigint
	, Label varchar(10)
	)

INSERT INTO ##_DriveSpace 
	EXEC master.dbo.xp_fixeddrives


-- Iterate through drive letters.
DECLARE  curDriveLetters CURSOR
	FOR SELECT driveletter FROM ##_DriveSpace

DECLARE @DriveLetter char(1)
	OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN

		 SET @cDrive = 'GetDrive("' + @DriveLetter + '")' 

			EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT 

				IF @Result = 0  

					EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT 

				IF @Result = 0  

					EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT 

				IF @Result = 0  

					EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT 

				IF @Result = 0  

					EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT 

				IF @Result <> 0  
 
					EXEC sp_OADestroy @Drv 
					EXEC sp_OADestroy @objFSO 

			SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )

			SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )

			INSERT INTO ##_DriveInfo
				VALUES (@DriveLetter, @Size, @Free, @Label)

	END
	FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END

CLOSE curDriveLetters
DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''

-- Produce report.
SELECT DriveLetter
	, Label
	, FreeSpace AS [FreeSpace MB]
	, (TotalSpace - FreeSpace) AS [UsedSpace MB]
	, TotalSpace AS [TotalSpace MB]
	, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC	
GO

DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo

mikeyw
Starting Member

1 Post

Posted - 2013-01-10 : 09:23:35

in SQL2008 R2 SP1 onwards just use the new DMF / DMV :
SELECT DB_NAME(f.database_id) DatabaseName,
f.FILE_ID, size DBSize, file_system_type,
volume_mount_point, total_bytes, available_bytes
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.FILE_ID);

HGanesh
Starting Member

1 Post

Posted - 2013-04-29 : 06:00:34

Hi Haywood,

The above written script is just awesome. But i have one query here...
In our environment there is one monitoring server from where all the server are being monitored. When I use this query it just fetches the current server's drive details. Could you please throw some light on how i run this OLE Automation scripts for all the servers. I want pass @server as server name which i can get from master database from another SP.

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-06-01 : 11:40:14

Haywood's script generates


SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

This is an instance of SSMS 2008R2 running on my own pc?!

What "security configuration"? This is my machine FFS?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-06-04 : 15:58:20

quote:
Originally posted by Rasta Pickles

Haywood's script generates

SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

This is an instance of SSMS 2008R2 running on my own pc?!

What "security configuration"? This is my machine FFS?

You would run sp_configure to enable the option. You can also do it in Surface Area Configuration. The error message tells you what to enable.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gsa
Starting Member

2 Posts

Posted - 2013-12-02 : 12:23:25

Hi,

Please I wish to exclude from the drive list one drive disk - D : who is possible?
Thanks

Hadrian

gsa
Starting Member

2 Posts

Posted - 2013-12-03 : 05:44:48

Hi,

Please I wish to exclude a disk from the drive list.It's possible?
Thanks,

Hadrian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-03 : 12:19:33

quote:
Originally posted by gsa

Hi,

Please I wish to exclude a disk from the drive list.It's possible?
Thanks,

Hadrian

There are several scripts in this thread, so we are unsure which one you are referring to. It would be best if you started a new thread on this topic and showed us which query you want to use/modify.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Goondla Siva
Starting Member

1 Post

Posted - 2014-04-16 : 01:57:09

quote:
Originally posted by gvphubli

there is one more article at this page
http://www10.brinkster.com/technologyyogi/newsite/Articles.htm

- = Cracky DBA = -
http://www.geocities.com/gvphubli/

Hi....i need total disk space in MB's how?

siva prasad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources