Forum FAQ: How do I use the OLE Automation Stored Procedures to monitor the disk space?

Questions:

How do I use the OLE Automation Stored Procedures to monitor the disk space? For example, when the available disk space falls below 20%, we can get an alert.

Answer:

We need to calculate the total space and free space of disks, and then use Database Mail to send mail. We can refer to the following statements:

SET NOCOUNT ON

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),

 FreeSpace INT NULL,

 TotalSize INT NULL,

 PercentFree DECIMAL(4,2)

 )

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

UPDATE Drives SET PercentFree=CAST(FreeSpace AS FLOAT)/TotalSize*100

IF EXISTS( SELECT 1 FROM Drives WHERE PercentFree<=20)

  BEGIN

          EXEC msdb..sp_send_dbmail

          @profile_name='TestProfile',

          @recipients='test@example.com',

          @subject='Alter',

          @body='This is the body of the test message',

          @query = ' SELECT * FROM Drives WHERE PercentFree<=20' ,

          @importance='High',

          @attach_query_result_as_file = 1 ,

          @execute_query_database='TestDB';

         --The results are returned as an attachment

  END

DROP TABLE Drives

When we execute the above statements, we need to enable the OLE Automation. By default, SQL Server blocks access to OLE Automation stored procedures because this component is turned off as part of the security configuration for this server. A system administrator can enable access to OLE Automation procedures by using sp_configure. Please see：

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

References: Database Mailhttps://msdn.microsoft.com/en-us/library/ms175887.aspx
OLE Automation Stored Procedures
https://msdn.microsoft.com/en-us/library/ms190501.aspx