Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Finding out Total Disk Space in TSQL Expand / Collapse
Author
Message
Posted Tuesday, January 08, 2008 11:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, January 09, 2008 7:46 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 10:47 AM
Points: 81, Visits: 280
Ive not tried it, but try this

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

Dave
Post #440663
Posted Wednesday, January 09, 2008 9:04 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:58 AM
Points: 3,924, Visits: 1,588
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
Posted Wednesday, January 09, 2008 9:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230
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
Posted Wednesday, January 09, 2008 11:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:45 AM
Points: 2,385, Visits: 1,843
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
Posted Thursday, January 10, 2008 12:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 35,969, Visits: 30,261
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #441024
Posted Thursday, January 10, 2008 3:42 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230
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
Posted Thursday, January 10, 2008 7:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, March 10, 2009 11:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, March 10, 2009 12:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:40 PM
Points: 202, Visits: 1,069
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse