Here is how to check the system drive space threshold in SQL Server. (You'll need to install SQL Mail first.)
- Create the stored procedure (System_DriveSpace).
- Execute the stored procedure as a job with parameters (Drive, Threshold, Global email address/individual email address).
1. Create the following stored procedure:
CREATE Procedure System_DriveSpace ( @DriveLetter char(1), @Threshold integer = null, @EmailTo varchar(100) = null ) As set nocount on declare @FreeSpace integer declare @MinAllowed integer declare @EmailMsg varchar(200) declare @EmailSub varchar (200) declare @ServerName varchar(20) create table #tmp_DiskSize (drive varchar(2), MB_free INT ) insert into #tmp_DiskSize exec master..xp_fixeddrives select @FreeSpace = MB_free from #tmp_DiskSize where drive = @DriveLetter if @Threshold is not null select @MinAllowed = @Threshold else select @MinAllowed = 1024 If @FreeSpace > @MinAllowed Select 'Plenty of space no need for alarm' Else begin select @Servername=@@ServerName Select 'Drive ' + @DriveLetter + ' on ' + @ServerName + ' is below minimum threshold contact dba immediately' if @EmailTo is not null begin select @EmailMsg = 'Drive ' + @DriveLetter + ' is below minimum threshold on ' + @ServerName + '. Only ' + cast(@FreeSpace as varchar) + 'Mb remaining. Escalate to DBA and Infrastructure team immediately' select @EmailSub = '**** CRITICAL CONDITION on ' + @ServerName + ' ****' EXEC master.dbo.xp_sendmail @recipients = @EmailTo, @subject = @EmailSub, @message = @EmailMsg end end drop table #tmp_DiskSize return
2. Create the following job:
------------------------- exec master..system_drivespace 'C', '512', 'firstname.lastname@example.org' exec master..system_drivespace 'D', '1024', 'email@example.com' exec master..system_drivespace 'E', '2048', 'firstname.lastname@example.org'
Now, schedule the job accordingly.