SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Get total disk size and free disk space
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 11/13/2007 :  11:32:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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



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

gvphubli
Yak Posting Veteran

India
54 Posts

Posted - 12/18/2007 :  14:56:41  Show Profile  Visit gvphubli's Homepage  Reply with Quote
there is one more article at this page
http://www10.brinkster.com/technologyyogi/newsite/Articles.htm

- = Cracky DBA = -
http://www.geocities.com/gvphubli/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 12/18/2007 :  15:26:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 12/18/2007 :  15:31:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://en.wikipedia.org/wiki/Volume_Mount_Point



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 12/18/2007 :  18:49:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

RyanAustin
Yak Posting Veteran

Canada
50 Posts

Posted - 03/25/2008 :  19:03:35  Show Profile  Click to see RyanAustin's MSN Messenger address  Reply with Quote
The compiled dll works great for a 2005 SQL instance, but is there anything that would work for a 2000 instance??

Edited by - RyanAustin on 03/25/2008 19:04:54
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 03/25/2008 :  20:32:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

PABluesMan
Starting Member

26 Posts

Posted - 03/26/2008 :  12:00:54  Show Profile  Reply with Quote
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.

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

Hope this helps!

I geek, therefore I am
Go to Top of Page

Haywood
Posting Yak Master

USA
221 Posts

Posted - 03/26/2008 :  12:29:53  Show Profile  Reply with Quote
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

Go to Top of Page

mikeyw
Starting Member

1 Posts

Posted - 01/10/2013 :  09:23:35  Show Profile  Reply with Quote
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);
Go to Top of Page

HGanesh
Starting Member

1 Posts

Posted - 04/29/2013 :  06:00:34  Show Profile  Reply with Quote
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.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 06/01/2013 :  11:40:14  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 06/04/2013 :  15:58:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

gsa
Starting Member

2 Posts

Posted - 12/02/2013 :  12:23:25  Show Profile  Reply with Quote
Hi,

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

Hadrian
Go to Top of Page

gsa
Starting Member

2 Posts

Posted - 12/03/2013 :  05:44:48  Show Profile  Reply with Quote

Hi,

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

Hadrian
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 12/03/2013 :  12:19:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Goondla Siva
Starting Member

India
1 Posts

Posted - 04/16/2014 :  01:57:09  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000