Log Shipping Error with 14221

1

Forum Patrons,

Your advise in the past was great and after this problem with Log Shipping error couldn't find the relevant answer from BOL.

The Log Shipping was broken and found the intruder on the Secondary server where the operation on to Sharing File Drive has performed with FILE Transfer where the COPY - LOG BACKUPS were storing.

Date 25/06/2018 6:12:00 PM
Log Job History (LSAlert_DRserver)

Step ID 1
Server DRserver
Job Name LSAlert_DRserver
Step Name Log shipping alert job step.
Duration 00:00:00
Sql Severity 16
Sql Message ID 14421
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: xxxxx\x-sql-dr. The log shipping secondary database DRserver.DB1_DEV has restore threshold of 6 minutes and is out of sync. No restore was performed for 406 minutes. Restored latency is 6 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421). The step failed.

Primary Server ALERT and BACKUP working.

Secondary Server COPY working but not RESTORE ans subsequently ALERT is failing with above error.

Your expert advise will surely help me as in the previous occasions.

Thanks in advance

2

Error 14221 indicates that the difference between the last backed up file and last restored file is greater than the time selected for the Out of Sync Alert threshold. You can troubleshoot Log shipping Error message 14221 by hitting below thread:
http://www.sqlmvp.org/troubleshooting-log-shipping-issues-in-sql-server/

3

Thanks Jason for sharing your thought and also providing the link to understand.

I have the problem in identifying the CHAIN OF LOG SEQUENCES.

Digging further deep, PRIMARY - ALTER and BACKUP were working fine.

SECONDARY - COPY fine whereas, RESTORE Failed due to someone disturbed the DB on Secondary by querying / accessing through Select Statement / in my case someone created File transfer onto the Shared Drive where the LOGS were storing. As a Result of this RESTORE and subsequently ALERT (14221 Error) gets failed.

Is there any way that can be queried and able to grab the SEQUENTIAL ORDER for the Transaction Log be identified to restore? ( TRANSACTION LOG STATUS from the SSMS is not that helpful in identifying the Sequence).

Me learning the Order and Thanks for identifying the problem on behalf of me.

Hope through you would learn the right hit against the LS.

4

May I request Jason Clark to please respond.

Many thanks

5

Thanks Jason Clark