I cobbled together this script from bits on the internet. It works fine, but needs OLE Automation to be enabled on the server. Should you wish to do this, use the Surface-Area configuration tool.

— “DiskSpaceUsed.sql” eg: C-Drive 65.3GB used
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint

SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate ‘Scripting.FileSystemObject’,@fso OUT
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur
FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0
BEGIN

EXEC @hr = sp_OAMethod @fso,’GetDrive’, @odrive OUT, @drive
EXEC @hr = sp_OAGetProperty @odrive,’TotalSize’, @TotalSize OUT
UPDATE #drives

SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END

CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso

SELECT Drive,
cast((TotalSize – FreeSpace)/1024.0 as decimal(4,2)) as ‘Used(GB)’
FROM #drives
ORDER BY drive
DROP TABLE #drives

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