Alert on low disk space

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

-- space_alert.sql

     declare @trig int, @drive varchar(50), @free varchar(50), @email varchar(300), @operator varchar(50)
     set @trig = 15 set @operator = 'some name'

-- capture free-space to temp-table

     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
     begin
          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
          begin
               set @email = 'EXECUTE msdb.dbo.sp_notify_operator @name=N''' + @operator + ''', @subject=N''Space Alert (SomeServer ' + @drive + @free + '%)'',@body=N''On someserver The Volume ' + @drive + ' has only ' + @free + '% free space.'''
               exec(@email)
          end

-- then remove current line from table

          delete from #space where drive = @drive
     end