Script to Findout Free Disk Space

SET NOCOUNT ON



IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')

DROP TABLE ##_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')

DROP TABLE ##_DriveInfo



DECLARE @Result INT

, @objFSO INT

, @Drv INT

, @cDrive VARCHAR(13)

, @Size VARCHAR(50)

, @Free VARCHAR(50)

, @Label varchar(10)

CREATE TABLE ##_DriveSpace

(

DriveLetter CHAR(1) not null

, FreeSpace VARCHAR(10) not null

)

CREATE TABLE ##_DriveInfo

(

DriveLetter CHAR(1)

, TotalSpace bigint

, FreeSpace bigint

, Label varchar(10)

)

INSERT INTO ##_DriveSpace

EXEC master.dbo.xp_fixeddrives



-- Iterate through drive letters.

DECLARE curDriveLetters CURSOR

FOR SELECT driveletter FROM ##_DriveSpace

DECLARE @DriveLetter char(1)

OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

SET @cDrive = 'GetDrive("' + @DriveLetter + '")'

EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT

IF @Result = 0

EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT

IF @Result <> 0



EXEC sp_OADestroy @Drv

EXEC sp_OADestroy @objFSO

SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )

SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )

INSERT INTO ##_DriveInfo

VALUES (@DriveLetter, @Size, @Free, @Label)

END

FETCH NEXT FROM curDriveLetters INTO @DriveLetter

END

CLOSE curDriveLetters

DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'

PRINT ''

-- Produce report.

SELECT DriveLetter

, Label

, FreeSpace AS [FreeSpace MB]

, (TotalSpace - FreeSpace) AS [UsedSpace MB]

, TotalSpace AS [TotalSpace MB]

, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##_DriveInfo

ORDER BY [DriveLetter] ASC

GO

DROP TABLE ##_DriveSpace

DROP TABLE ##_DriveInfo