go backsqlschool blogs list

Monitor Disk Space for SQL Server databases

by Antonios Chatzipavlis





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


Ημερομηνία: 16 August 2018 16:34
Αξιολόγηση: ( 1 )
Κατηγορίες:
Share it:

Σχετικά Blog Post

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.