dbWriter SQL stored procedure email drive space problems to
administrator
Set up job to run this stored procedure on hourly basis to check for
drive space problems and email to the administrator: Here are two
choices of how to send that e-mail.
Send e-mail using xp_sendmail:
CREATE PROCEDURE [dbo].[ZP_EMAIL_Drive]
As
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 = 'administrator@YOURSITE.com',
@subject ='SERVER X - 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 = 'F'
-- Free Space on F drive Less than Threshold
if @MB_Free < 2048
exec master.dbo.xp_sendmail
@recipients = 'administrator@YOURSITE.com',
@subject ='SERVER X - Free Space Issue on C Drive',
@message = 'Free space on F Drive
has dropped below 2 gig'
drop table #FreeSpace
GO
|
OR Send e-mail using
dbWriters -- CDONTS NewMail
CREATE PROCEDURE [dbo].[ZP_EMAIL_Drive]
As
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 [dbo].[zp_sendMAIL]
@From = 'SERVER X ',
@To = 'administrator@YOURSITE.com',
@CC = null,
@Subject = 'SERVER X - Free Space Issue on C Drive',
@Body = 'Free space on C Drive
has dropped below 1 gig',
@BCC = null,
@Attachment = null
select @MB_Free = MB_Free from #FreeSpace where Drive = 'F'
-- Free Space on F drive Less than Threshold
if @MB_Free < 2048
exec [dbo].[zp_sendMAIL]
@From = 'SERVER X ',
@To = 'administrator@YOURSITE.com',
@CC = null,
@Subject = 'SERVER X - Free Space Issue on F Drive',
@Body = 'Free space on F Drive
has dropped below 2 gig',
@BCC = null,
@Attachment = null
drop table #FreeSpace
GO
|
|
