SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Alerts not firing


Alerts not firing

Author
Message
Matthew Darwin
Matthew Darwin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4879 Visits: 880
Hi All,

I have log shipping configured on a primary server, along with the alerts that are created by default.

This morning, I encountered a problem with the log shipping, caused by the disk that the logs are being backed up to becoming full. However, despite the LSAlert job failing and raising the 14220 error, and this error being logged in the SQL Server Logs, the alert didn't fire.

The alert has been working before, checking the history it last fired in January and notifications were sent.

However, now it does not seem to be working.

I've tried restarting the SQL Server Agent service but that has had no effect.

Any ideas where to look next?

Thanks

Matthew

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA
sqlbuddy123
sqlbuddy123
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25710 Visits: 2243
Did you configure alerts on top of the LS Alert Job ?

--
SQLBuddy
Matthew Darwin
Matthew Darwin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4879 Visits: 880
The alert is the stock one created when configuring log shipping as follows:-

EXEC msdb.dbo.sp_add_alert @name=N'Log shipping Primary Server Alert.', 
@message_id=14420,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'



The job successfully raises the error, and this is logged in the SQL Server log.

If I manually raise the error, then it still doesn't fire the trigger.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA
sqlbuddy123
sqlbuddy123
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25710 Visits: 2243
Matthew Darwin (3/20/2014)
The alert is the stock one created when configuring log shipping as follows:-

EXEC msdb.dbo.sp_add_alert @name=N'Log shipping Primary Server Alert.', 
@message_id=14420,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'



The job successfully raises the error, and this is logged in the SQL Server log.

If I manually raise the error, then it still doesn't fire the trigger.


Check if the Operator & Notifications are enabled.

Also check in the SQL Server Agent properties, if you have enabled the Mail Profile.

--
SQLBuddy
Matthew Darwin
Matthew Darwin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4879 Visits: 880
These are configured.

To be clear; the alert is not firing, not that emails aren't being sent. The alert was previously working. The only change I've made since the last time it worked was to change the notification threshold; and this appears to be working correctly. The job fails as expected and the error is logged. However the error being logged is not causing the alert to fire, the counter and date on the alert history are not increased.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA
sqlbuddy123
sqlbuddy123
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25710 Visits: 2243
Matthew Darwin (3/20/2014)
These are configured.

To be clear; the alert is not firing, not that emails aren't being sent. The alert was previously working. The only change I've made since the last time it worked was to change the notification threshold; and this appears to be working correctly. The job fails as expected and the error is logged. However the error being logged is not causing the alert to fire, the counter and date on the alert history are not increased.


Then it should have something to do with LS config itself. As you made changes to the threshold, may be try to recreate the alert or break the LS and set it up again and check it.

--
SQLBuddy
Matthew Darwin
Matthew Darwin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4879 Visits: 880
I tried this, but no definitely nothing to do with the log shipping configuration. It seems far more to be to do with alerts in general; I know this because I've set up a different alert on a custom error message, configured that to notify an operator, run a RAISERROR calling the alert and seeing that be sent to the SQL Server Error log but no alert fired.

So log shipping is a red herring; it's entirely to do with SQL Server not firing alerts when it should.

Any thoughts on where to look to resolve this?

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA
sqlbuddy123
sqlbuddy123
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25710 Visits: 2243
Matthew Darwin (3/27/2014)
I tried this, but no definitely nothing to do with the log shipping configuration. It seems far more to be to do with alerts in general; I know this because I've set up a different alert on a custom error message, configured that to notify an operator, run a RAISERROR calling the alert and seeing that be sent to the SQL Server Error log but no alert fired.

So log shipping is a red herring; it's entirely to do with SQL Server not firing alerts when it should.

Any thoughts on where to look to resolve this?


First check this ..

Also check if the Operator is enabled or not. Rt click on alert and see if Enabled CheckBox is checked. And in the Notifications tab check for the option "View Notifications sent to this user by " , Alerts has been selected.

And for the Alert, check if "Notify Operators" has been checked and the Operator has been selected with Email option.

Also check if your Failsafe Operator has been enabled in the SQL Server Agent properties.

If the above is all good then ..

Start SQL Profiler and try to trigger an alert using RAISERROR and check if anything gets captured in the profiler trace. Also check the SQL Error Log, Windows Application & System logs for any message around this time.

--
SQLBuddy
Matthew Darwin
Matthew Darwin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4879 Visits: 880
You don't need an operator set to trigger an alert.

I have a test alert set as follows:-

EXEC msdb.dbo.sp_add_alert @name=N'Test Alerts', 
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@event_description_keyword=N'Test Alert Error',
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO



I then test it using the following:-

RAISERROR('Test Alert Error', 16,1) WITH LOG



The error is raised and written to the SQL Server Log, but the alert is not fired (checking the alert history number of occurrences remains 0).

I have then created the exact same alert on another server, and this does fire and increments the alert history.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA
sqlbuddy123
sqlbuddy123
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25710 Visits: 2243
Matthew Darwin (3/20/2014)
The alert has been working before, checking the history it last fired in January and notifications were sent.


I was thinking about alert notifications. How are you using alerts without notifications ?

First Check if the Alerts are enabled or not. If yes, then check the SQL Server\ SQL Agent \ Application Log for any error messages. Also check if the error is getting logged in Application Log.

What's the version of the current server and another server where the alerts are firing ? Also check for the SQL Agent service account permission differences ..

--
SQLBuddy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search