SQL Server – Check Disk Usage

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

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
Bookmark the permalink.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *