reporting total disk space

Has anyone got a way of reporting total disk space for
each drive on a server ?
I can use the DOS DIR command to report free space, but I
want to also get total space !
TIA.You can use xp_fixeddrives. It isn't documented, nor supported so all the regular warnings apply.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:01d701c3769e$8d9ca6f0$a001280a@.phx.gbl...
> Has anyone got a way of reporting total disk space for
> each drive on a server ?
> I can use the DOS DIR command to report free space, but I
> want to also get total space !
> TIA.
>|||Jim
Check it out
CREATE table DriveTable (Drive varchar(10),[MB Free] int)
INSERT into Drivetable Exec xp_fixeddrives
SELECT * FROM DriveTable
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:01d701c3769e$8d9ca6f0$a001280a@.phx.gbl...
> Has anyone got a way of reporting total disk space for
> each drive on a server ?
> I can use the DOS DIR command to report free space, but I
> want to also get total space !
> TIA.
>|||Hi Jim,
As well as the xp_fixeddrives the information is also easily available
through WMI scripting.
Here's one I found somewhere and modified to work in DTS as an active x
script. But I now mainly use a windows scripting host version to go get disk
information off all available servers. It was needed as I was told that
xp_fixeddrives doesn't work properly on either SAN/Clusters/multi instanced
servers (or a combination of all three). Not sure why/how xp_fixeddrives
doesn't work or under which scenarios, but had to get the WMI version
working all the same.
Function Main()strServer = "SomeMachineSomewhere"Const HARD_DISK = 3set
objLocator = CreateObject("WbemScripting.SWbemLocator")Set objServices =objLocator.ConnectServer(strServer, "root\cimv2")
objServices.Security_.ImpersonationLevel = 3 Set colInstances =objServices.ExecQuery("SELECT * FROM Win32_LogicalDisk WHERE DriveType = " &
HARD_DISK & "")For Each Drive In colInstances If Not IsNull(Drive.Size)
Then MsgBox ("Drive " & Drive.deviceid & " Total Size: "
&round(Drive.Size/1024/1024/1024,1) & " Free Space: "
&round(Drive.FreeSpace/1024/1024/1024,1) ) Else MsgBox ("Drive "
& Drive.deviceid & " is not available.") End IfNext Main =DTSTaskExecResult_SuccessEnd FunctionRegards,Nick"Jim Trowbridge"
<jtrowbridge@.adelaidebank.com.au> wrote in message
news:01d701c3769e$8d9ca6f0$a001280a@.phx.gbl...
> Has anyone got a way of reporting total disk space for
> each drive on a server ?
> I can use the DOS DIR command to report free space, but I
> want to also get total space !
> TIA.
>|||This function will give you total space for any given drive:
CREATE FUNCTION dbo.GetDriveSize
(@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
--
David Portas
--
Please reply only to the newsgroup
--
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:01d701c3769e$8d9ca6f0$a001280a@.phx.gbl...
> Has anyone got a way of reporting total disk space for
> each drive on a server ?
> I can use the DOS DIR command to report free space, but I
> want to also get total space !
> TIA.
>|||Yet another way :-)
use master
go
CREATE PROCEDURE sp_diskspace
AS
SET NOCOUNT ON
DECLARE @.hr int
DECLARE @.fso int
DECLARE @.drive char(1)
DECLARE @.odrive int
DECLARE @.TotalSize varchar(20)
DECLARE @.MB bigint ; 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,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
go
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:01d701c3769e$8d9ca6f0$a001280a@.phx.gbl...
Has anyone got a way of reporting total disk space for
each drive on a server ?
I can use the DOS DIR command to report free space, but I
want to also get total space !
TIA.