March 20, 2014 at 5:54 am
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[/url]
March 20, 2014 at 10:42 am
Did you configure alerts on top of the LS Alert Job ?
--
SQLBuddy
March 20, 2014 at 10:59 am
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[/url]
March 20, 2014 at 11:33 am
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
March 20, 2014 at 12:04 pm
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[/url]
March 20, 2014 at 12:54 pm
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
March 27, 2014 at 6:16 am
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[/url]
March 27, 2014 at 8:23 am
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
March 27, 2014 at 8:33 am
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[/url]
March 27, 2014 at 9:14 am
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
March 27, 2014 at 9:33 am
sqlbuddy123 (3/27/2014)
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
When the alert was working yes, it was sending emails. However, whilst troubleshooting I'm taking this down to the barebones in order to try and find the problem, so I'm not bothering with the operator etc to take all of that out of the equation.
Both servers are running the same version, and they use the same service account for the SQL Server Agent.
Checking the SQL Server Agent log there is nothing other than a stop and start request from me today:-
2014-03-27 12:26:17 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...
2014-03-27 12:26:20 - ! [097] Memory leak detected [8 bytes]
2014-03-27 12:26:20 - ? [098] SQLServerAgent terminated (normally)
Running a trace against errors gives the following:-
User Error Message - Test Alert Error
Error Log - 2014-03-27 14:55:07.05 spid91 Error: 50000, Severity: 16, State: 1.
2014-03-27 14:55:07.05 spid91 Test Alert Error
Error Log - Error: 50000 Severity: 16 State: 1 Test Alert Error
From doing a little reading, I'm wondering if this is to do with performance counters, as per this post; however I've followed the steps in this article but that doesn't seem to have fixed it. The only thing I've not carried out since doing that is a reboot of the server; which I'll have to wait for a maintenance window to do.
If that doesn't fix it, I really don't know what to try next.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
March 27, 2014 at 10:23 am
Matthew Darwin (3/27/2014)
When the alert was working yes, it was sending emails. However, whilst troubleshooting I'm taking this down to the barebones in order to try and find the problem, so I'm not bothering with the operator etc to take all of that out of the equation.
Both servers are running the same version, and they use the same service account for the SQL Server Agent.
Checking the SQL Server Agent log there is nothing other than a stop and start request from me today:-
2014-03-27 12:26:17 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...
2014-03-27 12:26:20 - ! [097] Memory leak detected [8 bytes]
2014-03-27 12:26:20 - ? [098] SQLServerAgent terminated (normally)
Running a trace against errors gives the following:-
User Error Message - Test Alert Error
Error Log - 2014-03-27 14:55:07.05 spid91 Error: 50000, Severity: 16, State: 1.
2014-03-27 14:55:07.05 spid91 Test Alert Error
Error Log - Error: 50000 Severity: 16 State: 1 Test Alert Error
Looks clean .. What is SP level of this SQL Server ?
From doing a little reading, I'm wondering if this is to do with performance counters, as per this post; however I've followed the steps in this article but that doesn't seem to have fixed it. The only thing I've not carried out since doing that is a reboot of the server; which I'll have to wait for a maintenance window to do.
If that doesn't fix it, I really don't know what to try next.
I don't think it's performance counters issue here. But nothing wrong in giving it a try ..
And sometimes restart could fix instability issues ..
As a workaround may be you can forward events from this server to a different server and kick the alerts from there ..
Check this
--
SQLBuddy
March 27, 2014 at 10:27 am
Both are fairly old versions; but I don't think it's a version issue as both servers are running the same: 10.50.1600.
It used to work on one and now doesn't; and it still works on the other.
I'll check out that article in the meantime.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
March 27, 2014 at 10:38 am
Matthew Darwin (3/27/2014)
Both are fairly old versions; but I don't think it's a version issue as both servers are running the same: 10.50.1600.It used to work on one and now doesn't; and it still works on the other.
I'll check out that article in the meantime.
That's RTM version. It's time for you to consider applying SP2 which may fix the issue .. And SQL Server bugs are fixed in the later service packs.
Sometimes these issue may occur due to the SQL Server interaction with other server components like windows updates, hotfixes, S\W etc ..
--
SQLBuddy
March 27, 2014 at 10:40 am
I'm aware that it's an old version; however both servers are identical in terms of patching at the OS and SQL level, so I don't believe upgrading will cure at this point.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply