Sql Server Error 1474 State 1
SQL Server – Hide system objects in Object Explorer – SQL Server Management Studio SQL Server – How to get last access/update time for a table SQL Server – Displaying line numbers in Query Editor – SSMS. If we have gained access or can gain access to SQL server with different logon credentials then always use spreaderrorlog, xpreaderrorlog or use the GUI option of opening up Management node – SQL server Logs – View – SQL server log. Same sample shown above looks like.
Summary: This tutorial shows you how to stop the log shipping alert which is sending SQL Server error 14421. Apart from this, we will also discuss the reasons for SQL Log Shipping error 14421.
Problem Description
“We were in the middle of some pretty intense maintenance of SQL Server last night. Then, the primary database MDB file was badly fragmented. After that, we maintained a test copy of this database to test and set up log shipping on the test database. One of us deleted the test database even without removing the log shipping first. Now, when we are using the log shipping in SQL Server, it is throwing following error message:
The log shipping secondary database SERVER.database has restore the threshold of 45 minutes and now it is out of sync. No restore was executed for 10022 minutes. The latency of restore is 0 minutes. We have been checked the agent log and logshipping monitor information. Even we have removed everything and our research leads us to believe that this error is occurring due to the backup job. We are still trying to operate it, however, not getting any appropriate solution to overcome this error. Well, it is not a big deal but SQL Server error is prompting every couple of minutes in the log. Is there anything we can do to fix SQL error: 14421, severity: 16, state: 1.? Thanks in advance!”
SQL Log Shipping error 14421 – Possible Causes
SQL Server has a Log shipping feature that automates the backup of transaction log files on a primary database server then, restore them onto a standby server. Using this feature, one can restore the database at the time of disaster recovery.
Sometimes the log shipping feature disrupts the application functioning due to improper synchronization inside the server. This interruption occurs in the form of an error prompt that says ‘SQL error 14421 Severity 16 State 1’. It means some issues have occurred during the database recovery from backup file and the restoration process gets failed.
Apart from this, there are multiple other reasons that may result error 14421 severity 16 state 1 in SQL Server, such as:
- Setting up incorrect value for the Backup Alert threshold
- Backup job is failed on the primary server
- The restore job is not running
- Restore folder path is not valid
- Modified date/ time on the monitor or primary server
- Data storage disk has no space
- log_shipping_primaries table not updated with current values
- Change in the schedule of the job
- Primary server is not connected to monitor server msdb database
- Invalid entries in Windows registry
- Different timings of Monitor server and the primary server
SQL Error: 14421, severity: 16, state: 1 – Solution
As discussed above that SQL Server error 14421 does not only occurs due to the log shipping failure yet, there may several other reasons like server or machine issues. Hence, below are some solutions that can resolve the SQL log shipping error 14421:
1. Check if the SQL Server Agent Service is operating for the secondary server instance. Also, check that the restore job in secondary database is enabled and scheduled for running at the suitable frequency.
2. As it is possible that the secondary server might be failing hence, check the job history of restore job to detect the main cause. If the permissions are not enabled then, enable them and restart your server.
Error 1479 Severity 16 State 1
3. Legend of zelda ocarina of time emulator. The backup alert threshold value might be incorrect. Basically, this value should be set up to at least three times frequency of the restore job. Hence, if you have changed the frequency of restore job when the log shipping is configured and starts functioning then, update the backup alert threshold value accordingly.
4. Examine the current authentication status between primary server and monitor. In addition, check if all the permission are configured properly. If not, then reconfigure them.
5. Update the monitor tables with latest data for your secondary database and run sp_refresh_log_shipping_monitor on the secondary server instance.
Conclusion
Sql Server Error 1474 State 1 Line
Error 14421, , Severity: 16, State: 1 in SQL Server defines that the value of Out of Sync Alert threshold is exceeded between previously backed up file and restore file. Therefore, we have discussed some possible and simple solutions that can troubleshoot SQL error 14421 message quickly and seamlessly.