Monitor free space

I want an SQL script that will monitor free space on certain drives of
my SQL2000 server.
Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
a
SELECT freespace FROM xxxx WHERE (mydrive='C')
method that I can use?
TIA
Dave.Another way
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')
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>I want an SQL script that will monitor free space on certain drives of
> my SQL2000 server.
> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
> a
> SELECT freespace FROM xxxx WHERE (mydrive='C')
> method that I can use?
>
> TIA
> Dave.
>|||Sorry, change TotalSize to FreeSpace.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23oHy2rEyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Another way
> 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')
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184658104.570475.151950@.e16g2000pri.googlegroups.com...
>>I want an SQL script that will monitor free space on certain drives of
>> my SQL2000 server.
>> Is 'EXEC master..xp_fixeddrives' the only way to do this or is there
>> a
>> SELECT freespace FROM xxxx WHERE (mydrive='C')
>> method that I can use?
>>
>> TIA
>> Dave.
>