SMTP Notifications - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

SMTP Notifications

Author

Topic

krajdba
Starting Member

43 Posts

Posted - 2007-09-24 : 06:55:17

Hi

How to Create SMTP Notifications using SQL Server 2000.

Thanks & Regards

-Raj

Raj.

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-09-24 : 09:38:03

http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/

http://www.sql-server-performance.com/articles/dev/sending_sql_notifications_CDOSYS_p1.aspx

krajdba
Starting Member

43 Posts

Posted - 2007-10-12 : 05:47:00

Hi

How to implement alert notification mails on Operating System....

Processor
% Processor Time
Memory
Available Megabytes
Page Faults/Seconds
LogicalDisk
%Free Space etc

Urgent please.

Thanks

Raj.

AnimalMagic
Starting Member

28 Posts

Posted - 2007-10-12 : 12:03:07

quote:
Originally posted by krajdba

Hi

How to implement alert notification mails on Operating System....

Processor
% Processor Time
Memory
Available Megabytes
Page Faults/Seconds
LogicalDisk
%Free Space etc

Urgent please.

Thanks

Raj.

In EM go to alerts, right click -> new. then set up an alert based on a windows performance condition, and notify your operators in the response tab. Its all fairly straight forward to set up a simple alert.

krajdba
Starting Member

43 Posts

Posted - 2007-10-16 : 03:55:42

Hi

Could you please give me an example for %Free Space.

thanks

Raj.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 04:10:49

"example for %Free Space"
Disk space?
Database free space?
Log free space?
Something else?

"Urgent please."

How urgent? You got a reply within a few hours but its taken you 4 days to come back with your follow-up ...

Kristen

krajdba
Starting Member

43 Posts

Posted - 2007-10-16 : 11:28:00

Disk space,Log free space.........Please

thanks

Raj.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 11:52:48

Disk space = xp_fixeddrives

Log space = DBCC SQLPERF ( LOGSPACE )

Kristen

krajdba
Starting Member

43 Posts

Posted - 2007-10-17 : 03:31:22

Hi

Guess, if the hard disk space has reached 80%. I would like to get an alert mail.

How can we do this?

Thanks

Raj.

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 03:57:09

I would schedule a job that ran the Sproc and if the value was above the threshold send me an Email (or one of the other alerts available).

I don't think you can actually place an alert on that condition occurring (but I could be wrong!) hence the idea of a job scheduled to run every minute, or hour, depending on how quickly you would need to know.

Kristen

AnimalMagic
Starting Member

28 Posts

Posted - 2007-10-17 : 06:06:27

quote:
Originally posted by Kristen

I would schedule a job that ran the Sproc and if the value was above the threshold send me an Email (or one of the other alerts available).

I don't think you can actually place an alert on that condition occurring (but I could be wrong!) hence the idea of a job scheduled to run every minute, or hour, depending on how quickly you would need to know.

Kristen

Kristen,

Sorry to jump in on this thread, but i noticed you mentioned about using xp_fixeddrives to look at free disk space. I currently use this to alert me when a disk falls below a user defined MB size. I couldnt however find anything which told me the total disk size, and was therefore unable to work anything out on percentages (as mentioned above). How do you currently work out the total disk space?

Regards

John

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 06:48:39

We launch a Batch file which does:

DIR C:\*.* /ON /S
DIR D:\*.* /ON /S
...

and we store all changed file details in the DB. We can then attempt to predict size of individually changing files over time - and alert if some virus or Muppet

changes a bunch of file dates - as Slammer or one of those intruders did, and our ISP claimed their firewall wouldn't let it through and it was impossible that ANY files on our system had been changed by it. The DIR listing from the day before / after showed otherwise and they are no longer our ISP. Result!

Kristen

AnimalMagic
Starting Member

28 Posts

Posted - 2007-10-17 : 06:51:51

For you both - and anyone else that wants it, i managed to find a script and amend it a bit to satisfy the request above. Thanks to the following link for the script

http://www.lazydba.com/sql/1__16047.html

And here is the finished SP script (with the email bit added, and 2 variables added)

-- Example
-- exec sp_diskspace 10,10 (will send an email when any drive is below 10% free)

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_diskspace
@CThreshold int,
@OtherThreshold int
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB Numeric
SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
/*
SELECT
drive, FreeSpace as 'Free(MB)', TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' FROM #drives
ORDER BY drive
*/

declare @EmDrv varchar(3)
declare @Emfree int
declare @msg varchar(100)
DECLARE emailcursor CURSOR LOCAL FAST_FORWARD FOR
SELECT drive,CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) from #drives ORDER by drive
OPEN emailcursor
FETCH NEXT FROM emailcursor INTO @EmDrv, @Emfree
WHILE @@FETCH_STATUS=0
BEGIN
if @EmDrv = 'C' and @EmFree <= @CThreshold
Begin
set @msg = 'Disk space on C is less than the threshold set: Down to ' + cast(@Emfree as varchar(10)) + '% Free'
exec master..xp_sendmail @recipients = 'JohnMo', @message = @msg
End
if @EmDrv <> 'C' and @EmFree <= @OtherThreshold
Begin
set @msg = 'Disk space on C is less than the threshold set: Down to ' + cast(@Emfree as varchar(10)) + '% Free'
exec master..xp_sendmail @recipients = 'JohnMo', @message = @msg
End
FETCH NEXT FROM emailcursor INTO @EmDrv, @Emfree
END
Close emailcursor
DEALLOCATE emailcursor

DROP TABLE #drives Return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-17 : 07:18:36

moved to script library.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources