SQL Server 2005 Log Shipping LS - Alert

Question

Post reply

SQL Server 2005 Log Shipping LS - Alert

SQL DBA-685905

Say Hey Kid

Points: 670
More actions
April 7, 2009 at 12:43 am

#129774

We have configured logshipping on one Test server. In that backup job(primary server) , copy and restore jobs(secondary server) are running fine. But the LS-Alert job in the secondary server is running for every 2 minutes and giving an error message shown in the below.

We increased the delay threshold latency by 3 times. But it didnt work.

Message

Executed as user: sa. The log shipping secondary database db_name has restore threshold of 60 minutes and is out of sync. No restore was performed for 10 minutes. Restored latency is 120 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421). The step failed.

Can anyone help me out?:-)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

tcnolan SSCrazy Points: 2097 More actions · Answer 1

Can you paste the results of the following query? It sounds like there is something misconfigured in the alert. Just replace db_name with the database name and then hide it from the results pasted in. Execute both queries on the primary and only the secondary query on the secondary server. With that information I think there may be a bit of light shed on the issue.

On primary server

SELECT * FROM msdb.dbo.log_shipping_monitor_primary WHERE primary_database='db_name'

SELECT * FROM msdb.dbo.log_shipping_monitor_secondary WHERE secondary_database='db_name'

On secondary server

SELECT * FROM msdb.dbo.log_shipping_monitor_secondary WHERE secondary_database='db_name'

SQL DBA-685905 Say Hey Kid Points: 670 More actions · Answer 2

SQL DBA-685905

Say Hey Kid

Points: 670

April 7, 2009 at 2:23 am

#973154

Since it is an urgent production issue, we have reconfigured logshipping in the box.

And log shipping configuration is successfull.

We found LS-Alert job is generating alerts for every 2 minutes.

Could you tell how to check whether we have done correct alert configuration?

Thanks for ur reply..

SQL DBA-685905 Say Hey Kid Points: 670 More actions · Answer 3

Again LS_alert job is generating the alerts after reconfiguring the log shipping.

Please find the below error message.

Error: 14421, Severity: 16, State: 1.2009-04-08 11:26:00.05 spid53

The log shipping secondary database XXXXXXXXXX.DBNAME has restore threshold of 45 minutes and is out of sync. No restore was performed for 11 minutes. Restored latency is 60 minutes. Check agent log and logshipping monitor information.

2009-04-08 11:28:00.05 spid53

we got alerts for half an hour and it got resolved automatically..

Any idea??

SQL DBA-685905 Say Hey Kid Points: 670 More actions · Answer 4

We are facing the same alert agian.

The restore job is working fine, but we are getting the alerts.

Please find the error message.

The log shipping secondary database server_name.db_name has restore threshold of 45 minutes and is out of sync. No restore was performed for 488 minutes. Restored latency is 45 minutes. Check agent log and logshipping monitor information.

2009-04-13 06:40:00.86 spid53 Error: 14421, Severity: 16, State: 1.

The alert job in secondary server is giving alerts for every 2 minutes.

Any help?

maechismo_8514 SSCoach Points: 17510 More actions · Answer 5

maechismo_8514

SSCoach

Points: 17510

April 15, 2009 at 5:05 pm

#978516

Hi,

14420 and 14421 are known errors in Log shipping. Rather than me explaining check this out :

http://support.microsoft.com/kb/329133

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

parthi-1705

SSCrazy Eights

Points: 9277

October 28, 2009 at 12:38 pm

#1071987

It means that you have not backup your database before configuring into log shipping u have to take the Backup of Py server backup and restore in your sec server and then try.

Thanks
Parthi

@SQLNumpty SSCrazy Points: 2920 More actions · Answer 7

Two things to check:

What is the time difference between the name of last file to be copied and the system clock? This should be only a little more that the frequency of your TLog backups and is your file copy delay.

From the SQL logs, what is the time difference between the time stamp entry and the name of the above file? This difference minus the time above is your log restore delay.

You may find that one or both of the above will be way out from what you expected. If the log restore delay is large, make sure you are only attempting to process files that have not already been processed.

Let us know how you get on.