Job Code
declare @threshold_space_pct decimal(5,2) = 99.000;
declare @ebody nvarchar(max) ='';
with r as (
select distinct v.volume_mount_point as drive
, v.total_bytes as drive_size_in_bytes
, v.available_bytes as drive_free_space_in_bytes
, p.drive_free_space_pct
, iif (p.drive_free_space_pct<=@threshold_space_pct,1,0) as low_space
from sys.master_files AS f
cross apply sys.dm_os_volume_stats(f.database_id, f.file_id) as v
cross apply ( values (cast ( (v.available_bytes * 1.0 ) / (v.total_bytes * 1.0 ) as decimal(5,2) ) * 100.0 ) ) as p(drive_free_space_pct)
)
select @ebody += drive + ' detected with low space ' + CHAR(10)+CHAR(13) from r where low_space = 1 ;
if ( len(@ebody) > 0 )
begin
declare @subject nvarchar(1024) = 'Low disk space detected in SQL Server instance ' + @@Servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourProfile',
@recipients = 'dba@sqlschool.gr',
@body = @ebody,
@subject = @subject
end
//antonch