Finding out Total Disk Space in TSQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Finding out Total Disk Space in TSQL

20 posts, Page 1 of 2

1 2 »»

Finding out Total Disk Space in TSQL

Rate Topic

Display Mode

Topic Options

Author

Message

Raymond Entena

Posted Tuesday, January 08, 2008 11:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 09, 2012 4:21 AM
Points: 2, Visits: 35

anyone know any workaround in TSQL who can give this result set?

Drive letter | Total Disk Space (MB) | Free Disk Space (MB)
-----------------------------------------------------------
C 150000 100000
D 160000 130000
Q 100000 90000
S 300000 200000
T 300000 280000

i can get the drive letters and free disk space in MB from "xp_fixeddrives" but i don't know how to get the total disk space in MB using TSQL...

thanks in advance...

Post #440435

hb21l6

Posted Wednesday, January 09, 2008 7:46 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 06, 2013 7:13 AM
Points: 78, Visits: 277

Ive not tried it, but try this

http://www.lazydba.com/sql/1__16047.html

Dave

Post #440663

SanjayAttray

Posted Wednesday, January 09, 2008 9:04 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT
drive, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives
ORDER BY drive
DROP TABLE #drives
GO

SQL DBA.

Post #440743

Michael Valentine Jones

Posted Wednesday, January 09, 2008 9:21 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:35 PM
Points: 2,969, Visits: 10,620

You can get this information with the PSINFO utility available on the link below.
http://technet.microsoft.com/en-us/sysinternals/bb896649.aspx

Sample command and output:

psinfo \\MyServerName -D disk

PsInfo v1.73 - Local and remote system information viewer
Copyright (C) 2001-2005 Mark Russinovich
Sysinternals - www.sysinternals.com

System information for \\MyServerName:

Volume Type       Format     Label                      Size       Free   Free
    A: Removable                                                          0.0%
    C: Fixed      NTFS       MyServer_C             19.99 GB   14.92 GB  74.7%
    D: Fixed      NTFS       MyServer_D             12.00 GB    9.41 GB  78.5%
    Z: CD-ROM                                                             0.0%

Post #440757

free_mascot

Posted Wednesday, January 09, 2008 11:27 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:11 AM
Points: 1,790, Visits: 1,320

Hello Raymond,

Here is the simple command which will give you desired result.

xp_fixeddrives

Hope this is what are you looking for.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Post #441008

Jeff Moden

Posted Thursday, January 10, 2008 12:45 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 33,113, Visits: 27,041

Heh... you didn't read the OP's request... he wants the total disk size as well. xp_FixedDrives does not provide that information. ;)

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #441024

Michael Valentine Jones

Posted Thursday, January 10, 2008 3:42 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:35 PM
Points: 2,969, Visits: 10,620

Jeff Moden (1/10/2008)

Heh... you didn't read the OP's request... he wants the total disk size as well. xp_FixedDrives does not provide that information. ;)

Not to mention that the OP said he used xp_fixeddrives, and it didn't give him all the info he needed.

Post #441516

Raymond Entena

Posted Thursday, January 10, 2008 7:38 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 09, 2012 4:21 AM
Points: 2, Visits: 35

Thanks a bunch guys! Really appreciate this although I have created a workaround... I used FSUTIL command line and did some tweaking to get the result I need and I also compared it with the result from xp_fixedrives..

Below is the code.. I have one problem though... When I tried converting
the Bytes to Mbytes at the last SELECT statement, I got this error.

"Server: Msg 8114, Level 16, State 5, Line 42
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to bigint."

Help please! :):):)

------------------------------------------------------------------
SET NOCOUNT ON

DECLARE @varSQL varchar(1000), @varDrive varchar(10)

CREATE TABLE #tmpDriveSpaceInfo
(drive varchar(10),
xpFixedDrive_FreeSpace_MB bigint,
FSutil_FreeSpace_Bytes integer,
FSutil_Space_Bytes integer,
FSutil_AvailSpace_Bytes integer
)

CREATE TABLE #tmpFSutilDriveSpaceInfo
(drive varchar(10),
info varchar(50)
)

INSERT INTO #tmpDriveSpaceInfo (drive, xpFixedDrive_FreeSpace_MB)
EXEC master..xp_fixeddrives

DECLARE CUR_DriveLooper CURSOR FOR SELECT drive FROM #tmpDriveSpaceInfo

OPEN CUR_DriveLooper
FETCH NEXT FROM CUR_DriveLooper INTO @varDrive
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'EXEC master..XP_CMDSHELL ' + ''''+ 'fsutil volume diskfree ' + @varDrive + ':' + ''''
INSERT INTO #tmpFSutilDriveSpaceInfo (info)
EXEC(@varSQL)
UPDATE #tmpFSutilDriveSpaceInfo SET drive = @varDrive WHERE drive IS NULL
FETCH NEXT FROM CUR_DriveLooper INTO @varDrive
END

DELETE FROM #tmpFSutilDriveSpaceInfo WHERE info IS NULL

SELECT drive,
ltrim(rtrim(left(info,29))) as InfoType,
ltrim(rtrim(substring (info, charindex (':',info) + 2, 20))) as Size_Bytes
INTO #tmpFSutilDriveSpaceInfo_Fixed
FROM #tmpFSutilDriveSpaceInfo

SELECT a.drive,
a.xpFixedDrive_FreeSpace_MB,
(SELECT cast(Size_Bytes as bigint) FROM #tmpFSutilDriveSpaceInfo_Fixed WHERE drive = a.drive and InfoType = 'Total # of free bytes')/1048576 AS FSutil_FreeSpace_MB,
(SELECT cast(Size_Bytes as bigint) FROM #tmpFSutilDriveSpaceInfo_Fixed WHERE drive = a.drive and InfoType = 'Total # of bytes')/1048576 AS FSutil_TotalSpace_MB,
(SELECT cast(Size_Bytes as bigint) FROM #tmpFSutilDriveSpaceInfo_Fixed WHERE drive = a.drive and InfoType = 'Total # of avail free bytes')/1048576 AS FSutil_AvailableSpace_MB
FROM #tmpDriveSpaceInfo a

CLOSE CUR_DriveLooper
DEALLOCATE CUR_DriveLooper
DROP TABLE #tmpFSutilDriveSpaceInfo
DROP TABLE #tmpDriveSpaceInfo
DROP TABLE #tmpFSutilDriveSpaceInfo_Fixed
------------------------------------------------------------------

Post #441554

osama hussein

Posted Tuesday, March 10, 2009 11:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 21, 2012 2:17 PM
Points: 2, Visits: 44

for SQL 2005 or 08 you need to enable the OLE Automation sps

here is the full query with enable and then disable the OLEs

/*******************************************************/
/* Enabling Ole Automation Procedures */
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
/*******************************************************/
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT @@Servername
SELECT
drive, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives
ORDER BY drive
DROP TABLE #drives
GO
/*******************************************************/
/* Disabling Ole Automation Procedures */
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
/*******************************************************/
GO

Post #672654

Mike - CI

Posted Tuesday, March 10, 2009 12:36 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 14, 2013 12:27 PM
Points: 197, Visits: 1,034

Just as another option if you work at a shop that does not allow Ole Automation to be turned on (some consider it a security risk), you can do the same thing with Powershell without having automation turned on (for that matter you don't need to have SQL on the server at all....if you ever need to keep track of this on non-SQL boxes).

There are lots of really good articles posted by Chad Miller, and with them you can put together all kinds of great information using powershell.
http://www.sqlservercentral.com/Authors/Articles/Chad_Miller/421/

Post #672696

« Prev Topic | Next Topic »

20 posts, Page 1 of 2

1 2 »»

Permissions