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: Friday, July 18, 2014 1:26 PM
Points: 656, Visits: 467
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 @ 6:57 PM
Points: 7,094, Visits: 12,583
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
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: Friday, July 18, 2014 1:26 PM
Points: 656, Visits: 467
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 @ 6:57 PM
Points: 7,094, Visits: 12,583
You're welcome. Thanks for the feedback.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1420062
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse