Log shipping with Trouble Shooting in SQL Server include Error message 14420 and error message 14421 that occur when you use log shipping in SQL Server

Log Shipping in SQL Server is a very old and popular feature most widely used to mostly enable disaster-recovery solution for the application databases. SQL Server Log shipping allows us to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

Log shipping is a Database level not server level if you add database users it will reflect on secondary but not login could not reflect due to server level limitation to reflect login we need sp_help_rev_login by manual work

Monitoring Log Shipping

After you have configured log shipping, you can monitor information about the status of all the log shipping servers. The history and status of log shipping operations are always saved locally by the log shipping jobs. The history and status of the backup operation are stored at the primary server, and the history and status of the copy and restore operations are stored at the secondary server. If you have implemented a remote monitor server, this information is also stored on the monitor server.

You can configure alerts that will fire if log shipping operations fail to occur as scheduled. Errors are raised by an alert job that watches the status of the backup and restore operations. You can define alerts that notify an operator when these errors are raised. If a monitor server is configured, one alert job runs on the monitor server that raises errors for all operations in the log shipping configuration. If a monitor server is not specified, an alert job runs on the primary server instance, which monitors the backup operation. If a monitor server is not specified, an alert job also runs on each secondary server instance to monitor the local copy and restore operations.

Important:

To monitor a log shipping configuration, you must add the monitor server when you enable log shipping. If you add a monitor server later, you must remove the log shipping configuration and then replace it with a new configuration that includes a monitor server. For more information, Furthermore, after the monitor server has been configured, it cannot be changed without removing log shipping first.

Ref:
https://msdn.microsoft.com/en-US/library/ms190224(v=SQL.90).aspx

You can query these tables to monitor the status of a log shipping session. For example, to learn status of log shipping, check the status and history of the backup job, copy job, and restore job. You can view specific log shipping history and error details by querying the following monitoring tables.

Table	Description
log_shipping_monitor_alert	Stores alert job ID.
log_shipping_monitor_error_detail	Stores error details for log shipping jobs. You can query this table see the errors for an agent session. Optionally, you can sort the errors by the date and time at which each was logged. Each error is logged as a sequence of exceptions, and multiple errors (sequences) can per agent session.
log_shipping_monitor_history_detail	Contains history details for log shipping agents. You can query this table to see the history detail for an agent session.
log_shipping_monitor_primary	Stores one monitor record for the primary database in each log shipping configuration, including information about the last backup file and last restored file that is useful for monitoring.
log_shipping_monitor_secondary	Stores one monitor record for each secondary database, including information about the last backup file and last restored file that is useful for monitoring.

Stored Procedures for Monitoring Log Shipping

Monitoring and history information is stored in tables in msdb, which can be accessed using log shipping stored procedures. Run these stored procedures on the servers indicated in the following table.

Stored procedure	Description	Run this procedure on
sp_help_log_shipping_monitor_primary	Returns monitor records for the specified primary database from thelog_shipping_monitor_primary table.	Monitor server or primary server
sp_help_log_shipping_monitor_secondary	Returns monitor records for the specified secondary database from thelog_shipping_monitor_secondary table.	Monitor server or secondary server
sp_help_log_shipping_alert_job	Returns the job ID of the alert job.	Monitor server, or primary or secondary server if no monitor is defined
sp_help_log_shipping_primary_database	Retrieves primary database settings and displays the values from thelog_shipping_primary_databases and log_shipping_monitor_primarytables.	Primary server
sp_help_log_shipping_primary_secondary	Retrieves secondary database names for a primary database.	Primary server
sp_help_log_shipping_secondary_database	Retrieves secondary-database settings from the log_shipping_secondary,log_shipping_secondary_databases and log_shipping_monitor_secondarytables.	Secondary server
sp_help_log_shipping_secondary_primary (Transact-SQL)	This stored procedure retrieves the settings for a given primary database on the secondary server.	Secondary server

We can also use as see below T-SQL 

SELECT * from dbo.sysjobs WHERE category_id = 6

SELECT * FROM [msdb].[dbo].[sysjobhistory]
where [message] like '%Operating system error%'
order by [run_date] , [run_time]

SELECT * FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
where [message] like '%Operating system error%'


SELECT * FROM [msdb].[dbo].[restorehistory]

To Know Backup Details of Primary:

(Kindly Run it below T-SQL on Primary)

use msdb

-- Assign the database name to variable below
DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'Northwind'
-- query
SELECT TOP (30) s.database_name
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type] WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC

,backup_finish_date

another method to know backup details as see below

SELECT
    s.database_name,s.backup_finish_date,y.physical_device_name
FROM
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
    (s.database_name = 'IEDB2008Test')
ORDER BY

    s.backup_finish_date DESC;

SELECT secondary_server,
secondary_database,
primary_server,
primary_database,
last_copied_file,
last_copied_date,
last_restored_file,
last_restored_date
from msdb.dbo.log_shipping_monitor_secondary

Select * from msdb.dbo.log_shipping_monitor_primary
select * from msdb.dbo.log_shipping_monitor_secondary

SELECT
    s.database_name,s.backup_finish_date,y.physical_device_name
FROM
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
    (s.database_name = 'LSTestdb01')
ORDER BY
    s.backup_finish_date DESC;

Solution:
Ref:
https://support.microsoft.com/en-us/kb/329133

Description of error message 14420 and error message 14421 that occur when you use log shipping in SQL Server

MORE INFORMATION

Log shipping uses Sqlmaint.exe to back up and to restore databases. When SQL Server creates a transaction log backup as part of a log shipping setup, Sqlmaint.exe connects to the monitor server and updates the log_shipping_primariestable with the last_backup_filename information. Similarly, when you run a Copy or a Restore job on a secondary server, Sqlmaint.exe connects to the monitor server and updates the log_shipping_secondaries table. 

As part of log shipping, alert messages 14220 and 14221 are generated to track backup and restoration activity. The alert messages are generated depending on the value of Backup Alert threshold and Out of Sync Alert threshold respectively.

The alert message 14220 indicates that the difference between current time and the time indicated by thelast_backup_filename value in the log_shipping_primaries table on the monitor server is greater than value that is set for the Backup Alert threshold.

The alert message 14221 indicates that the difference between the time indicated by the last_backup_filename in thelog_shipping_primaries table and the last_loaded_filename in the log_shipping_secondaries table is greater than the value set for the Out of Sync Alert threshold.

Troubleshooting Error Message 14420

By definition, message 14420 does not necessarily indicate a problem with log shipping. The message indicates that the difference between the last backed up file and current time on the monitor server is greater than the time that is set for the Backup Alert threshold.

There are serveral reasons why the alert message is generated. The following list includes some of these reasons:

1. The date or time (or both) on the monitor server is different from the date or time on the primary server. It is also possible that the system date or time was modified on the monitor or the primary server. This may also generate alert messages.
2. When the monitor server is offline and then back online, the fields in the log_shipping_primaries table are not updated with the current values before the alert message job runs.
3. The log shipping Copy job that is run on the primary server might not connect to the monitor server msdbdatabase to update the fields in the log_shipping_primaries table. This may be the result of an authentication problem between the monitor server and the primary server.
4. You may have set an incorrect value for the Backup Alert threshold. Ideally, you must set this value to at least three times the frequency of the backup job. If you change the frequency of the backup job after log shipping is configured and functional, you must update the value of theBackup Alert threshold accordingly.
5. The backup job on the primary server is failing. In this case, check the job history for the backup job to see a reason for the failure.

Troubleshooting Error Message 14421

By definition, message 14421 does not necessarily indicate a problem with Log Shipping. This message 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.

There are serveral reasons why the alert message is raised. The following list includes some of these reasons:

1. The date or time (or both) on the primary server is modified such that the date or time on the primary server is significantly ahead between consecutive transaction log backups.
2. The log shipping Restore job that is running on the secondary server cannot connect to the monitor server msdb database to update the log_shipping_secondaries table with the correct value. This may be the result of an authentication problem between the secondary server and the monitor server.
3. You may have set an incorrect value for the Out of Sync Alert threshold. Ideally, you must set this value to at least three times the frequency of the slower of the Copy and Restore jobs. If the frequency of the Copy or Restore jobs is modified after log shipping is set up and functional, you must modify the value of the Out of Sync Alert threshold accordingly.
4. Problems either with the Backup job or Copy job are most likely to result in "out of sync" alert messages. If "out of sync" alert messages are raised and if there are no problems with the Backup or the Restore job, check the Copy job for potential problems. Additionally, network connectivity may cause the Copy job to fail.
5. It is also possible that the Restore job on the secondary server is failing. In this case, check the job history for the Restore job because it may indicate a reason for the failure.

Error: Core Information Related to SQL Error 15105

While backup SQL Server database on shared disk within network if the backup file (.bak) denies access, it implies that SQL Error 15105 has occurred. The corresponding error seems similar to this:

"Cannot open backup device "\\xxxxxxxx\xxxx.bak". Operating System Error 5 (failed to retrieve text for this error. Reason: 15105). (Microsoft.SQLServer.Smo)"

Sometimes the error message fluctuating on machine may vary and appears like following mentioned syntax and this message addresses to the local disk on network;

"Operating System Error 5 (Access Denied)"

Resolution Procedures

• Free the drive volume to enable expansion of SQL backup files.
• Run defragmentation process and analyze the level for all volumes to ensure that it is more than 15%.
• You can also follow the under mentioned workaround to rectify SQL Error 15105 issue:
• Go to the location on shared network where the original folder locates.
• Right click on the folder and select "Sharing and Security" option.
• In the "Sharing" tab, click on "Permissions"
• Next, assign "Full Access" privilege to target account for which backup is generated and if you are not concerned about security, you may assign "Full Access" to "EVERYONE" for a particular period of time. Once the backup process is completed, you can change\ modify the assigned permissions.
• Once you are done with applying permissions and privileges, click on 'Apply' and then 'OK'.
• Afterwards, click on the "Security" tab and add the specified account to whom full access permissions are granted and assign appropriate privileges to the accounts.
• Click on 'Apply' and further select 'OK'.

Quicker Recovery for Corrupt BAK Files

Either of the following given methods may helps in resolving the existing error message. If none of the above stated work around\ measures seems to be helpful, there are possibilities that the SQL BAK file is dealing with corruption issue that leads to inaccessibility of the database backup files.

Since, the manual workarounds are long and time consuming procedures; these work-around\ measures can even results in damage of files. In that case, the only way to efficiently recover corrupt and inaccessible files is SQL Backup Recovery tool. The tool helps recovering SQL DB backup and log transactional files and offers to restore them in either "SQL Server Database" (version 2012 and lower) or "SQL compatible SQL Server Scripts" on local storage media.

Some of Error Screen shot see below

Logshipping secondary server is out of sync and transaction log restore job failing. 


Problem
You can see that your logshipping is broken. In the SQL Error log,  the message below is displayed : 

Error: 14421, Severity: 16, State: 1.
The log shipping secondary database myDB.logshippingPrimary has restore threshold of 45 minutes and is out of sync. No restore was performed for 6258 minutes.

Description of error message 14420 and error message 14421 that occur when you use log shipping in SQL Server

The Error message 14421 does not necessarily indicate a problem with Log Shipping. This message indicates that the difference between the last backed up file and last restored file is greater than the time selected for theOut
of Sync Alertthreshold.
1. you can check the local timings of backup and restore server to see any differences
2. You may have set an incorrect value for theOut of Sync Alertthreshold. Ideally, you must set this value to at least three times the frequency of the slower
of the Copy and Restore jobs.
Nice Article is available for this
http://support.microsoft.com/kb/329133
http://support.microsoft.com/default.aspx?scid=329133 

Logshipping secondary server is out of sync and LSRestore job failing

Logshipping secondary server is out of sync and transaction log restore job failing. 


Problem
You can see that your logshipping is broken. In the SQL Error log,  the message below is displayed : 

Error: 14421, Severity: 16, State: 1.
The log shipping secondary database myDB.logshippingPrimary has restore threshold of 45 minutes and is out of sync. No restore was performed for 6258 minutes.

Description of error message 14420 and error message 14421 that occur when you use log shipping in SQL Server
http://support.microsoft.com/default.aspx?scid=329133 

Cause
Inside the LSRestore job history, you can find out two kind of messages  : 

– Restore job skipping the logs on secondary server 

Skipped log backup file. Secondary DB: ‘logshippingSecondary’, File: ‘\\myDB\logshipping\logshippingPrimary_20090808173803.trn’

– Backup log older is missing

*** Error 4305: The file ‘\\myDB\logshipping\logshippingPrimary_20090808174201.trn’ is too recent to apply to the secondary database ‘logshippingSecondary’. 
**** Error : The log in this backup set begins at LSN 18000000005000001, which is too recent to apply to the database. An earlier log backup that includes LSN 18000000004900001 can be restored.

Transaction Log backups can only be restored if they are in a sequence. If the LastLSN field and the FirstLSN field do not display the same number on consecutive transaction log backups, they are not restorable in that sequence. There may be several reasons for transaction log backups to be out of sequence. Some of the most common reasons are a redundant transaction log backup jobs on the primary server that are causing the sequence to be broken or the recovery model of the database was probably toggled between transaction log backups.


Resolution
At this time, to check if there are a gaps in the Restore Process. You can run the query below to try to find out whether a redundant Backup Log was performed : 

SELECT 
    s.database_name,s.backup_finish_date,y.physical_device_name
FROM 
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE 
    (s.database_name = ‘databaseNamePrimaryServer’)ORDER BY 
    s.backup_finish_date DESC;
 

You can see that another Backup Log was running out of logshipping process. Now, you have just to restore this backup on the secondary and run the LSRestore  Job.

SQL SERVER LOG SHIPPING COMMON ERRORS OR FAILURES

Below are some of the common errors which may be raised during setup or configuration of Logshipping

Error((An entry for primary server , primary database does not exist on this secondary. Register the primary first. (Microsoft SQL Server, Error: 32023))
– This error can occur, if there are any incorrect changes to the logshipping configuration.
Example: if you changed the path of backup share and updated the new share details in Logshipping configuration window, it may not take it into affect for copy and restore jobs, in such cases, it is better to use T-SQL instead like below

1 2 3 4 5

exec master.dbo.sp_change_log_shipping_secondary_primary @primary_server = LS_PrimaryServerName, @Backup_source_directory= 'BackupSharePath' @primary_database = DBName, @file_retention_period =14420

Error: 14412, Severity: 16
The destination database TestDB is already part of a log shipping plan.
This database TestDB is already log shipping.
Error: 14424, Severity: 16
The database TestDB is already involved in log shipping.
– These errors occur if you try to add a database to logshipping configuration using
sp_add_log_shipping_database stored Procedure.
– A database can be part of only one logshipping configuration. Verify existing log shipping configuration and alter or remove existing setup if it is not required.
– If logshipping is already removed, then there may be remnants of previous log shipping configuration, so you need to cleanup log shipping metadata.

Error: 14420, Severity: 16, State: 1
The log shipping destination is out of sync by minutes.
Error: 14420, Severity: 16, State: 1
The log shipping primary database has backup threshold of minutes and has not performed a backup log operation for minutes. Check agent log and logshipping monitor information.
– There may be various reasons why these alerts are generated, some of them include
o The date or time on the monitor server may be different from the date and time on the primary server. Also, it is possible that the system date or time was modified on the monitor or the primary server.
o When the monitor server is offline and got back online, the fields in the log_shipping_primaries table are not updated with the current values before the alert message job runs.
o The log shipping Copy job that is run on the primary server might not connect to the monitor server msdb database to update the fields in the log_shipping_primaries table. This may be the result of an authentication problem between the monitor server and the primary server.
o You may have set an small or incorrect value for the Backup Alert threshold. Ideally, you must set this value to such a value based on your SLA thresholds and frequency of the backup job.
o Backup job on the primary server may be failing, in which case, we need to check the history of backup job and for any error messages in Primary server SQL errorlog.

Error: 14421, Severity: 16, State: 1
The log shipping destination is out of sync by %s minutes.
Error: 14421, Severity: 16, State: 1
The log shipping secondary database has restore threshold of minutes and is out of sync. No restore was performed for minutes. Restored latency is minutes. Check agent log and logshipping monitor information.
– There may be various reasons why these alerts are generated, some of them include
o The date or time on the primary server is modified in such a way that the date or time on the primary server is significantly ahead between consecutive transaction log backups.
o The log shipping Restore job that is running on the secondary server cannot connect to the monitor server msdb database to update the log_shipping_secondaries table with the correct value. This may be the result of an authentication problem between the secondary server and the monitor server.
o You may have set an incorrect value for the Out of Sync Alert threshold. Ideally, you must set this value to such a value based on your SLA thresholds and frequency of the copy or restore jobs.job.
o Copy or Restore jobs might have failed on the secondary server in which case, we need to check the history of copy job and Restore job and for any error messages in secondary server SQL errorlog.

Error: 18456, Severity: 14, State: 51.
Login failed for user ‘test’.
Reason: Failed to send an environment change notification to a log shipping partner node while revalidating the login.
Error: 18342, Severity: 10
Error: 18350, Severity: 10
– These messages can occur if using the SQL Server Service accounts of Primary, Secondary or Monitor are unable to communicate with each other.
– The error could be related to UAC, so run SQL Server Management Studio (SSMS) as Administrator.
– Issues with domain controller causing authentication problems.
– Make sure using SQL Server service account of primary can connect to Secondary and vice-verse.

Error: 32015, Severity: 16
The primary database cannot have SIMPLE recovery for log shipping to work properly.
– This error occurs if database which we are trying to configure as logshipping primary in Simple recovery model.
– Basic functioning of log shipping include, performing log backups on primary, copy them to secondary and restoring them on secondary with norecovery or standby. So, in order to use logshipping, database needs to be in Simple of Bulk-Logged recovery model without which log backups cannot be performed.

Error: 32017, Severity: 16
Logshipping is supported on Enterprise, Developer and Standard editions of SQL Server. This instance has Express Edition and is not supported.
– Log Shipping in SQL Server is supported in Enterprise, Developer, Standard, Web and Workgroup editions of SQL Server. So make sure the log shipi primary and secondary servers are running supported edition of SQL Server.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

http://www.sqlserverf1.com/tag/the-log-shipping-primary-database-has-backup-threshold-of-minutes-and-has-not-performed-a-backup-log-operation-for-minutes/

Change SQL Server log shipped database from Restoring to Standby Read-Only

We have an application for which SQL Server Log Shipping is configured as the disaster recovery (DR) solution. When we first setup log shipping we didn't have a need to use this database as a read-only copy. The use of this database has grown and we have many more read requests than we originally planned. To overcome the server load, we decided to use the secondary server as a read-only server. Our goal is to use the primary server for normal OLTP operations and the secondary server for all reporting and SELECT transactions. The database is very large and we want to convert the secondary log shipped database to read-only, but we don't want to have to redo the log shipping setup.

In this tip, I will explain the step by step process to change the secondary database from NORECOVERY (restoring) to STANDBY (read only) without having to reinitialize the log shipping configuration.

Solution

SQL Server Log Shipping allows you to automatically send transaction log backups from a primary database server instance to one or more secondary database server instances. In most cases, log shipping is setup so you have a warm standby server for disaster recovery. SQL Server offers the ability to have the secondary database in a restoring state where the database cannot be used or in a standby state which allows read-only activity.

Secondary log shipped databases can be kept in one of two modes:

• Restoring - This mode is also known as NORECOVERY mode and cannot be used for read operations.
• Standby / Read-Only - This mode is also known as STANDBY mode and can be used for read operations.

When you use the NORECOVERY mode, the database will be in a restoring state and inaccessible to users. When you use the STANDBY mode, the database will be in Read-Only state and users can access this database for read operations. Transactions which are in process on the primary or any uncommitted transactions cannot be read on the secondary database when the database is in a read-only state.

Steps to change the restore mode of secondary database in SQL Server Log Shipping

Check the current SQL Server database state

As previously mentioned, we have SQL Server Log Shipping in place for an application. First, let's see whether our log shipping is working or not. We will launch the log shipping dashboard report to check the log shipping status. Status should be GOOD to make sure both databases are in sync. To run this report, right click on server name, choose "Reports" and then "Standard Reports" followed by "Transaction Log Shipping Status" as shown below.

Once you will click on "Transaction Log Shipping Status", a report will run and appear in the right pane of SQL Server Management Studio. Below is a screenshot of that report. We can see the status is shown as "Good" which means both databases are in sync. We can also get information about the last copied and restored files along with the time since the last copied and restore file was applied.

We can also get this information by running the below command in the msdb database of the secondary server. You can see the last copied and restored file date then check it with the last backup file date.

SELECT secondary_server,
secondary_database,
primary_server,
primary_database,
last_copied_file,
last_copied_date,
last_restored_file,
last_restored_date
from msdb.dbo.log_shipping_monitor_secondary

Check the Current SQL Server Log Shipping Mode

Now check the secondary database mode either in SQL Server Management Studio or by using T-SQL. You can check it by running the below command. Look at the restore_mode column.  If the restore_mode value is 1 then it is in Standby mode and if it is 0 then it is in Restoring mode. For our test database it is currently in the Restoring mode since the value is 0.

SELECT secondary_database,
restore_mode,
disconnect_users,
last_restored_file
 FROM msdb.dbo.log_shipping_secondary_databases

We can also check this setting in SQL Server Management Studio by expanding the database folder on the secondary server. Below we can see it is in a "Restoring" state.

Change a SQL Server Log Shipping Database to Read-Only

There are two options to make such changes for log shipping configurations: 1) by running the log shipping system stored procedure and 2) by making changes to log shipping using SSMS.

Run the below command to change this configuration setting using T-SQL:

EXEC sp_change_log_shipping_secondary_database

@secondary_database = 'Collection',
@restore_mode = 1,
@disconnect_users =1

We can also change the restore mode from standby to restoring by running the same command with @restore_mode 0.

We can also apply this change in SSMS, which I will show in a different tip.

Check the SQL Server Log Shipping Change

Now, we can run the same script which we have executed above to check whether the restore mode has changed. We know 1 is for standby and 0 is for no recovery mode. We can see below that this has changed, but this change will not be reflected in SSMS until the next restore job has run successfully.

Run SQL Server Log Shipping Restore

Now go ahead and run the restore job on the secondary server. Once the restore has successfully completed, a *.tuf file will be automatically created under the data drive folder and the secondary database status will be changed to Standby mode in SSMS as well. Please see the below screenshot of the TUF file which is created under the data folder.

Check Log Shipping Status and Read-Only State

The restore job ran successfully, but we still need to check whether the Log Shipping configuration is working. Run the log shipping backup job on the primary server and once this job completes then run the copy and restore jobs on the secondary server to replicate all pending logs on the standby database. Once all three jobs complete successfully, we can run the transaction log shipping status report to check the log shipping status to see if both databases are in sync after this change. Here you can see the time stamp of the TUF file in the above screenshot and the time stamp of the last restored file in the below screenshot. It's clear that all logs have been applied on the secondary database after the change was made.

Lastly, we can verify log shipping configuration by creating an object in the primary database and then run the backup, copy and restore jobs to check that the object is created in the secondary database. Run the below script on the primary server to create a dummy table named "Manvendra" in the primary database.

--Run this on primary database on primary Server
USE [Collection]
GO
CREATE TABLE [dbo].[Manvendra](
 [Name] [nchar](10) NULL,
 [Salary] [nchar](10) NULL
) 

GO

Now run the log shipping backup job on the primary server and once this job completes run the copy and restore jobs on the secondary server to move the changes to the standby database. Once the restore job completes, check the secondary database to verify that the new table "Manvendra" has been created. As we can see in the below screenshot, table Manvendra has been created.

https://www.mssqltips.com/sqlservertip/3574/change-sql-server-log-shipped-database-from-restoring-to-standby-readonly/

SQL SERVER – Log Shipping Restore Job Error: The file is too recent to apply to the secondary database

If you are a DBA and handled Log-shipping as high availability solution, there are a number of common errors that come that you would over a period of time become pro on resolving. Here is one of the common error which you must have seen:

Message
2015-10-13 21:09:05.13     *** Error: The file ‘C:\LS_S\LSDemo_20151013153827.trn’ is too recent to apply to the secondary database ‘LSDemo’.(Microsoft.SqlServer.Management.LogShipping) ***
2015-10-13 21:09:05.13     *** Error: The log in this backup set begins at LSN 32000000047300001, which is too recent to apply to the database. An earlier log backup that includes LSN 32000000047000001 can be restored.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
Above error is a shown in failure of the history of restore job. If the failure is more than configured thresholds, then we would start seen below error in SQL ERRORLOG on secondary also:
2015-10-14 06:22:00.240 spid60       Error: 14421, Severity: 16, State: 1.
2015-10-14 06:22:00.240 spid60       The log shipping secondary database PinalServer.LSDemo has restore threshold of 45 minutes and is out of sync. No restore was performed for 553 minutes. Restored latency is 4 minutes. Check agent log and logshipping monitor information.

To start troubleshooting, we can look at Job activity monitor on secondary which would fail with the below state:

f you know SQL transaction log backup basics, you might be able to guess the cause. If we look closely to the error, it talks about LSN mismatch. Most of the cases, a manual transaction log backup was taken. I remember few scenarios where a 3rd party tool would have taken transaction log backup of database which was also part of a log shipping configuration.Since we know the cause now, what we need to figure out is – where is that “out of band” backup? Here is the query which I have written on my earlier blog.



DECLARE @db_name VARCHAR(100)SELECT @db_name = 'LSDemo'

-- querySELECT TOP (30) s.database_name,m.physical_device_name,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,CASE s.[type] WHEN 'D'THEN 'Full'WHEN 'I'THEN 'Differential'WHEN 'L'THEN 'Transaction Log'END AS BackupType,s.server_name,s.recovery_modelFROM msdb.dbo.backupset sINNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_idWHERE s.database_name = @db_nameORDER BY backup_start_date DESC,backup_finish_date

Once we run the query, we would get list of backups happened on the database. This information is picked from MSDB database.

Below picture is self-explanatory.

Once we found the “problematic” backup, we need to restore it manually on secondary database. Make sure that we are using either norecovery or standby option so that other logs can be restored. Once file is restored, the restore job would be able to pick-up from the same place and would catch up automatically.