Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding out Total Disk Space in TSQL


Finding out Total Disk Space in TSQL

Author
Message
Raymond Entena
Raymond Entena
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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...
hb21l6
hb21l6
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 294
Ive not tried it, but try this

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

Dave
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
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.
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
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%



free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 2235
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."
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44981 Visits: 39869
Heh... you didn't read the OP's request... he wants the total disk size as well. xp_FixedDrives does not provide that information. Wink

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
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. Wink


Not to mention that the OP said he used xp_fixeddrives, and it didn't give him all the info he needed.
Raymond Entena
Raymond Entena
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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! SmileSmileSmile

------------------------------------------------------------------
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
------------------------------------------------------------------
osama hussein
osama hussein
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 49
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
Mike - CI
Mike - CI
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 1155
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search