Setup SQL Agent alerts for User Error Messages

Setup a simple alert with SQL agent

1

When I test the alert it does not trigger the alert

Capture 2

 

Since error is not being logged, we can verify in the sys.messages

Capture 3

Lets alter the message to log

Capture 4

 

SELECT * FROM sys.messages WHERE
language_id = 1033 AND severity = 16
and message_id = 2812
EXEC sp_altermessage @message_id = 2812, @parameter = ‘WITH_LOG’ ,@parameter_value = ‘True’

Test the alert again, now we can see number of occurrences increases to 1

Capture 5

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s