[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
Log Shipping [SQLSTATE 42000] (Error 14421)

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

Log Shipping [SQLSTATE 42000] (Error 14421).


Applies to: SQL Server 2014.
 

Problem Description.
 

One of my students at the university was trying to configure log shipping of a  sample SQL Server database between a primary server and a secondary server but the log shipping jobs were failing. He shared the following error with me via email:

Date 3/4/2016 8:36:00 PM
Log Job History (LSAlert_KevinR)

Step ID 1
Server KEVINR
Job Name LSAlert_KevinR
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: NT SERVICE\SQLSERVERAGENT. The log shipping primary database KEVINR.AdventureWorksDW2008 has backup threshold of 60 minutes and has not performed a backup log operation for 898 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14420) The log shipping secondary database KEVINR\KEVINR2.AdventureWorksDW2008 has restore threshold of 45 minutes and is out of sync. No restore was performed for 898 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421). The step failed.

 

Cause.


Examining the log shipping configuration, we found the SQL Server Agent on both instances configured with managed service accounts. The KB321247 Support article offers 3 choices for SQL Server service and SQL Server Agent service accounts on log shipping scenarios: a domain account (when the instances are located on an domain environment); a local network account (when network access is required by SQL Server services); the Local System account.

The backup share that was configured by the student to hold the transaction log backups did not exist also.


Solution.


We recommended the student to use one of the 3 options fro the service account provided on the KB321247.

He created the required shared folder and followed this step-by-step article on MSSQLTips.com.


 

 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.