Disk space Notification by Email

--Author : Addanki Reddyprasad
--Usage : According to threshold value the respective person will get notified


declare @MB_Free int

create table #FreeSpace(
Drive char(1), 
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives

select @MB_Free = MB_Free from #FreeSpace where Drive = 'C'

-- Free Space on C drive Less than Threshold
if @MB_Free < 1024
 exec master.dbo.xp_sendmail 
 @recipients ='addanki.prasad@mphasis.com',
 @subject ='SERVER ESHSQL02 - Free Space Issue on C Drive',
 @message = 'Free space on C Drive 
 has dropped below 1 gig'

select @MB_Free = MB_Free from #FreeSpace where Drive = 'D'

-- Free Space on D drive Less than Threshold
if @MB_Free < 5120 
 exec master.dbo.xp_sendmail 
 @recipients ='addanki.prasad@mphasis.com',
 @subject ='SERVER ESHSQL02 - Free Space Issue on D Drive',
 @message = 'Free space on D Drive 
 has dropped below 5 gig'

select @MB_Free = MB_Free from #FreeSpace where Drive = 'E'

-- Free Space on E drive Less than Threshold
if @MB_Free < 5120 
 exec master.dbo.xp_sendmail 
 @recipients ='addanki.prasad@mphasis.com',
 @subject ='SERVER ESHSQL02 - Free Space Issue on E Drive',
 @message = 'Free space on E Drive 
 has dropped below 5 gig'


select @MB_Free = MB_Free from #FreeSpace where Drive = 'I'

-- Free Space on I drive Less than Threshold
if @MB_Free < 5120 
 exec master.dbo.xp_sendmail 
 @recipients ='addanki.prasad@mphasis.com',
 @subject ='SERVER ESHSQL02 - Free Space Issue on I Drive',
 @message = 'Free space on I Drive 
 has dropped below 5 gig'

select @MB_Free = MB_Free from #FreeSpace where Drive = 'J'

-- Free Space on J drive Less than Threshold
if @MB_Free < 5120 
 exec master.dbo.xp_sendmail 
 @recipients ='addanki.prasad@mphasis.com',
 @subject ='SERVER ESHSQL02 - Free Space Issue on J Drive',
 @message = 'Free space on J Drive 
 has dropped below 5 gig'

drop table #FreeSpace