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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s