Below is the script I use to check disk usage.
USE [master]
begin
set nocount on
/*******************************************************/
/* Enabling Ole Automation Procedures */
exec sp_configure 'show advanced options', 1
RECONFIGURE with override
exec sp_configure 'Ole Automation Procedures', 1
RECONFIGURE with override
/*******************************************************/
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; 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 IF @hr <> 0 EXEC sp_OAGetErrorInfo
@fso
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
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive 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 IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT
drive, cast(TotalSize/1024.0 as decimal(8,2)) as 'Total(GB)', cast(FreeSpace/1024.0 as decimal(8,2)) as 'Free(GB)',cast(TotalSize/1024.0-FreeSpace/1024.0 as decimal(8,2)) as 'Used(GB)',cast((TotalSize/1024.0-FreeSpace/1024.0)/(TotalSize/1024.0)*100 as decimal (8,2)) as 'PercentUsed'
FROM #drives
ORDER BY drive
DROP TABLE #drives
/*******************************************************/
/* Disabling Ole Automation Procedures */
exec sp_configure 'show advanced options', 1
RECONFIGURE with override
exec sp_configure 'Ole Automation Procedures', 0
RECONFIGURE with override
/*******************************************************/
end
Leave a Reply