Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2005 ALERTS Not Working for non-logged events Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 12:26 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 12:18 PM
Points: 656, Visits: 451
SQL 2005 Enterprise X64 SP4
ALERTS: SQL SERVER EVENT ALERT ---->Severity

Has anyone noticed that the Alerts only seem to trigger when the "Event" is logged to the Errorlog?
Anything that does not end up in the log does not seem to produce an alert response or update the alert history.
I can recreate this easily:
RAISERROR('Error test, 16, 1) WITH LOG; -- This triggers alert successfully
RAISERROR('Error test, 16, 1); -- This will not

My problem is I would like non-user defined errors to trigger alerts.
For example: Msg 8134, Level 16, State 1, Line 1

Is this a known bug? Are there configuration settings to change what errors are recorded in the log?
Can I send all Severity 16 to the log? (without raiserror)

Thanks for your help.
Mark G.
Post #1419694
Posted Wednesday, February 13, 2013 3:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
These are the English messages that get logged automatically.

SELECT  *
FROM sys.messages
WHERE language_id = 1033
AND (
is_event_logged = 1
OR severity >= 19
)
ORDER BY message_id;

If you want to start logging error 8134, or any message that is not logged by default, you can use this:

DECLARE @message_id INT = 8134;

EXEC sys.sp_altermessage
@message_id = @message_id,
@parameter = 'WITH_LOG',
@parameter_value = 'TRUE';



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1419763
Posted Thursday, February 14, 2013 6:56 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 12:18 PM
Points: 656, Visits: 451
OPC, exactly what I was looking for.
Thank you very much!


Mark G.
Post #1420035
Posted Thursday, February 14, 2013 7:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
You're welcome. Thanks for the feedback.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1420062
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse