SQL SERVER 2005 DB MAIL

Question

Post reply

SQL SERVER 2005 DB MAIL

Sponge-592981

Ten Centuries

Points: 1225
More actions
November 12, 2008 at 5:49 pm

#215167

To Gurus:
I currently have alerts set up and configured for sql server 2005 db mail. I have tested the mail for alerts triggering error events and works fine. My issue comes into play when I want to have an alert sent out on an informational event (Not that it should matter but its for when the principal and mirror database switch roles)
My current settings are as follows:
USE [msdb]
GO
/****** Object: Alert [Database Mirroring] Script Date: 11/12/2008 15:26:59 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Database Mirroring',
@message_id=0,
@severity=10,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@database_name=N'Databasename',
@event_description_keyword=N'"The mirrored database " " is changing roles from "MIRROR" to "PRINCIPAL""',
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
Actual Information event in NT Application log I want to capture:
Event Type:Information
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:1480
Date:11/12/2008
Time:2:43:21 PM
User:N/A
Computer:WHDWISQL1
Description:
The mirrored database " " is changing roles from "PRINCIPAL" to "MIRROR" due to Failover.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: c8 05 00 00 0a 00 00 00 È.......
0008: 0a 00 00 00 57 00 48 00 ....W.H.
0010: 44 00 57 00 49 00 53 00 D.W.I.S.
0018: 51 00 4c 00 31 00 00 00 Q.L.1...
0020: 00 00 00 00 ....

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

There are a couple of things you should check.

First can you send a testmail using DB Mail by right clicking on DB Mail?

If yes, check if SQL agent is configured correctly to use the DBMail profile ?

If yes, check the DB Mail logfile for any errors.

[font="Verdana"]Markus Bohse[/font]

Sponge-592981 Ten Centuries Points: 1225 More actions · Answer 2

I have followed the instructions and confirmed the profile the DB Mail is using is configured in sql agent and I was also able to send a test email from both the principal and mirror server. I have checked both the logs in DB Mail on both servers and see only "Database process starting" and "Database process shutting down" entries as information events. The dates are not even the dates in which I was trying to send out alerts so not sure if that leads to anything.

I did read somewhere that database mirroring states are captured in WMI events which I can use to alert as well. Then its a matter of setting up broker services on the msdb and endpoints etc... I think it would be over kill and I just cant understand why I can not take a sql server log that writes to the application log and pass it the text to set off and alert.

Please let me know if you can think of anything else. 😀

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

I think the problem is that you try to use the error message. Did you try to use the error number 1480 instead?

EXEC msdb.dbo.sp_add_alert @name=N'Database Mirroring',

@message_id=1480,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=0,

@database_name=N'Databasename',

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

[font="Verdana"]Markus Bohse[/font]

Sponge-592981 Ten Centuries Points: 1225 More actions · Answer 4

The script I provided was re-enginered I set this up through the gui and yes I did try 1480. Still nothing. Every time I tried something I made sure the settings were consistent in both the mirror and principal alert configurations.

This is a great article as well to tell you how to do it, however it uses WMI events and I am baffeled as to why I cant use informational events to trigger alerts :w00t:

http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx