June 18, 2009 at 10:48 am
Hi,
I want to get an email every day using database mail regarding the the Drive Space on the Server(Total space, free space)
Could you plz tell me how can we do this.
June 18, 2009 at 12:24 pm
You can start by reading here[/url]. Don't forget to read the Join the Discussion part
June 18, 2009 at 1:01 pm
Thank you.
I went through the script and I changed it to use database mail as below;
EXEC msdb..sp_send_dbmail @recipients = 'Myname@abc.com',
@subject = @MailSubject,
@body = @AlertMessage,
@profile_name ='SQL1'
Now, when I execute the procedure,
exec usp_DiskFreeSpaceAlert
, I'm getting result as
4 rows effected
.(I have four drives.) But no Email is coming...
and then I just executed the below and I'm able get the email successfully.
EXEC msdb..sp_send_dbmail @recipients = 'Myname@abc.com',
@subject = 'Hi',
@body = 'AlertMessage',
@profile_name ='SQL1'
Please advice where I'm going wrong..
June 18, 2009 at 1:28 pm
Try this script.
I created a sql job that executes this script based on the schedule,
such as evry morning at 7 am.
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@email.com',
@query = 'master..xp_fixeddrives' ,
@subject = 'Alert: Available space on systemName',
@attach_query_result_as_file = 0 ;
June 18, 2009 at 1:48 pm
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@email.com',
@query = 'master..xp_fixeddrives' ,
@subject = 'Alert: Available space on systemName',
@attach_query_result_as_file = 0 ;
This scripts just gives how much space we have. But My goal is to get an email when the drive space is less than some GB like 20GB or less than x%?
please advice me
June 18, 2009 at 1:49 pm
Did you check the DBMail queue? It might give a hint on why it's not sent?
Also, if not mistaken, when you execute your SP, besides the "4 rows effected" it should also say "Mail Queued" ?
June 18, 2009 at 1:57 pm
not exactly, this email shows you how much space on every drive is AVAILABLE
drive MB free
----- -----------
C 17966
D 2541
k 7868
x 7358
z 0260
(5 rows affected)
June 18, 2009 at 2:37 pm
... right, but in the discussion there's a method using an OLE Object to obtain the total disk space. That, together with the Free/Available gives you both pieces of info....
June 18, 2009 at 3:43 pm
Did you check the DBMail queue? It might give a hint on why it's not sent?
Also, if not mistaken, when you execute your SP, besides the "4 rows effected" it should also say "Mail Queued" ?
I checked the database mail log, no clue from there. and I'm getting in the result as 4 rows effected only. But no "Mail Queued" message
and aslo not exactly,
this email shows you how much space on every drive is AVAILABLE
drive MB free
----- -----------
C 17966
D 2541
k 7868
x 7358
z 0260
(5 rows affected)
Yes, this script gives exactly how much space is left. But I need to get an email automatically when the available space is left <20GB something like that
This is the Exact sript I' m using
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_DiskFreeSpaceAlert] Script Date: 06/18/2009 14:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[usp_DiskFreeSpaceAlert]
@DriveCBenchmark int = 1024,
@OtherDataDriveBenchmark int = 2048
AS
-- This procedure sends out an alert message when hard disk space is below a predefined value. This procedure can be scheduled to run daily so that DBA can act quickly to address this issue.
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
DriveLetter CHAR(1) NOT NULL,
FreeMB INTEGER NOT NULL)
DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)
/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
EXEC master..xp_fixeddrives
SELECT @DiskFreeSpace = FreeMB FROM #disk_free_space where DriveLetter = 'C'
IF @DiskFreeSpace < @DriveCBenchmark
Begin
SET @MailSubject = 'Drive C free space is low on ' + @@SERVERNAME
SET @AlertMessage = 'Drive C on ' + @@SERVERNAME + ' has only ' + CAST(@DiskFreeSpace AS VARCHAR) + ' MB left. Please free up space on this drive. C drive usually has OS installed on it. Lower space on C could slow down performance of the server'
-- Send out email
EXEC msdb..sp_send_dbmail @recipients = 'Myname@abc.com',
@subject = @MailSubject,
@body = @AlertMessage,
@profile_name ='SQL1'
End
DECLARE DriveSpace CURSOR FAST_FORWARD FOR
select DriveLetter, FreeMB from #disk_free_space where DriveLetter not in ('C')
open DriveSpace
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
WHILE (@@FETCH_STATUS = 0)
Begin
if @DiskFreeSpace < @OtherDataDriveBenchmark
Begin
set @MailSubject = 'Drive ' + @DriveLetter + ' free space is low on ' + @@SERVERNAME
set @AlertMessage = @DriveLetter + ' has only ' + cast(@DiskFreeSpace as varchar) + ' MB left. Please increase free space for this drive immediately to avoid production issues'
-- Send out email
EXEC msdb..sp_send_dbmail @recipients = 'Myname@abc.com',
@subject = 'hi',
@body = 'AlertMessage',
@profile_name ='SQL1'
End
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
End
close DriveSpace
deallocate DriveSpace
DROP TABLE #disk_free_space
June 19, 2009 at 10:48 am
Could you please advice how to make it work?? I'm not getting an email when executing the procedure usp_DiskFreeSpaceAlert
June 22, 2009 at 1:46 pm
Could you please advice how to make it work?? I'm not getting an email when executing the procedure usp_DiskFreeSpaceAlert
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply