Monitor disk space

/** This script checks the current space of the disks and if the available space is lower that the threshold (GB in this case) sends out email**/


use MyAdminDB
GO

declare @from varchar(100),
@to varchar(200),
@subject varchar(100),
@threshold int -- number of MB under which to launch an alert
　
　
　
SET NOCOUNT ON

DECLARE @list nvarchar(2000) = '';

WITH core AS (
　
　
SELECT DISTINCT

　
　
s.volume_mount_point [Drive],
　
　
CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
　
　
FROM
　
　
sys.master_files f
　
　
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
　
　
)
　
　
　
　
　
　
　
　
　
SELECT @list = @list + ' ' + Drive + ', current space available =' + cast(cast(round([AvailableMBs]*1.000/1024.000,2) as numeric(36,2)) as varchar) + 'GB'
　
　
FROM core
　
　
WHERE AvailableMBs < @threshold
　
　
IF LEN(@list) > 3 BEGIN
　
　
DECLARE @msg varchar(500) = 'Disk Space les than 8GB Notification. The following drives are currently reporting less than '
　
　
+ CAST(@threshold/1024 as varchar(12)) + ' GB free: ' + @list
　
　
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile@mydomain.com',
@recipients = @to,
@subject = @subject,
@body = @msg
END

RETURN 0
　
　
　
　
　
GO