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

Technorati Tags: Free Disk Space,Disk Space,DiskSpace