Alert on low disk space

Here’s a script I wrote for SQL2008R2, to run hourly in a sql-job, and email me if the percentage of free disk-space is getting low.

-- space_alert.sql

     DECLARE @trig int = 40, -- percentage free
		@operator varchar(50) = 'DBA', -- operator with email address
		@drive varchar(50), @free varchar(50), @email varchar(300)

-- capture free-space to temp-table

	DROP TABLE #space ------------testing
     select volume_mount_point drive, cast(sum(available_bytes)*100 / sum(total_bytes) as int) Free
     into #space
     from sys.master_files f
     cross apply sys.dm_os_volume_stats(f.database_id, f.[file_id])
     group by volume_mount_point

-- loop through the table row-by-row

     while (select count(*) from #space) > 0
          set @drive = (select top 1 drive from #space order by drive)
          set @free = (select top 1 free from #space order by drive)

-- and send email if space low

          if @free < @trig
               set @email = 'EXECUTE msdb.dbo.sp_notify_operator @name=N''' + @operator + ''', @subject=N''Space Alert (' + @@servername + @drive + @free + '%)'',@body=N''On '+ @@servername  + ' the Volume ' + @drive + ' has only ' + @free + '% free space.'''

-- then remove current line from table

          delete from #space where drive = @drive

NOTE: I am using @@servername as there are only default instances here.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s