How To Monitor Disk Space In Sql Server

As a DBA Its very important to always keep an Eye on Disk Space usage.

The Below stored procedure will help you monitor the Disk Space Usage and trigger an Alert if the specified Threshold is reached which is less than 20% free space on all drives on Server.

USE [MASTER]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_DiskSpaceMonitor]
AS
BEGIN
exec sp_configure 'Ole Automation Procedures', 1
reconfigure
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @OPdrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB Numeric
SET @MB = 1048576
Declare @msg varchar(500)
Declare @myServerName nvarchar(50) = @@SERVERNAME
Declare @mySubject nvarchar(255)
Set @mySubject = 'ALERT: Following Drives size are over 80% Usage on '+@myServerName
--Note I have set my Limit to 20% You can do this to any percentage of your wish
SET @msg = 'Low Disk Space Notification. The following drives are currently reporting less than 20% of Total Disk Space: '+'
'
CREATE TABLE #MyDrives (drive char(1) PRIMARY KEY,
FreeSpace float NULL,
TotalSize float NULL,
Threshold float NULL)
INSERT #MyDrives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE DriveCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #MyDrives ORDER by drive
OPEN DriveCursor FETCH NEXT FROM DriveCursor INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @OPdrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr =sp_OAGetProperty @OPdrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @OPdrive
UPDATE #MyDrives
SET TotalSize=@TotalSize/@MB,
Threshold = (@TotalSize * cast((0.20) as float))/@MB -- Change 0.20 by your value to set your own threshold
WHERE drive=@drive FETCH NEXT FROM DriveCursor INTO @drive
End
Close DriveCursor
DEALLOCATE DriveCursor
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
IF EXISTS (SELECT null FROM #MyDrives WHERE [FreeSpace] < Threshold )
        BEGIN
                 DECLARE @list varchar(30)
                 SET @list = ''
                 SELECT @list = @list + ' ' + drive + ',' FROM #MyDrives WHERE [FreeSpace] < Threshold
                 SET @list = LEFT(@list, LEN(@list) -1)                 
                 SET @msg = @msg + @list
                 EXEC msdb.dbo.sp_send_dbmail             
            @recipients='Recipients Email ID seperated by semi-colon ex m@m.com;b@b.com',
            @subject = @mySubject,
            @body = @msg,
            @profile_name = 'ProfileName which is configured to SendEmail Here' ;
         END
DROP TABLE #MyDrives
exec sp_configure 'Ole Automation Procedures', 0
reconfigure
END
GO

All you need to do know is Schedule a Job in SQL Server Agent on Desired Interval. How to schedule a Job in SQL Server Agent can be found here.