Get Free Disk Space for SQL server 2000

As a DBA you always need to monitor various metrics of a Database server. Disk space is one such metric you gotta watch out for. Monitoring Disk space for SQL server 2000 is bit difficult through script. Just happened to write a Script for SQL server 2000 which gives you free disk space in GB for all existing drives on database server.

SET NOCOUNT ON

DECLARE @TEMP AS VARCHAR(4000)

DECLARE @MBfree int

DECLARE @CMD1 varchar(1000)

SET @MBfree = 0

SET @CMD1 = ''

CREATE TABLE #tbl_xp_fixeddrives

(Drive varchar(2) NOT NULL,

[FreeSpace] int NOT NULL)

INSERT INTO #tbl_xp_fixeddrives(Drive, [FreeSpace])

EXEC master.dbo.xp_fixeddrives

SELECT @TEMP ='

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'

EXEC(@TEMP)

SELECT drive+':'+' '+

CAST(CAST((FreeSpace/(((dbo.GetDriveSize(drive))/1024)/1024))*100.0 as int) as VARCHAR(4000))+'%'

from #tbl_xp_fixeddrives

DROP TABLE #tbl_xp_fixeddrives

SELECT @TEMP = 'USE '

SELECT @TEMP = @TEMP+'master '+'DROP FUNCTION dbo.GetDriveSize'

EXEC (@TEMP)

SET NOCOUNT OFF

GO