Log is not getting shipped in SQL Server Logshipping

Well Problem could be of different types. But common issues are few.

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.

To resolve older log missing we can either find out the old logs or restart the whole logshipping thing:

To find out the older logs:

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

If 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 3^rd 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.

-- Assign the database name to variable below
DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'LSDemo'
-- 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

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.

You also can apply the logs manually by getting the restore commands:

WITH full_backups AS

(

SELECT

ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY database_name ASC, backup_finish_date DESC) AS [Row Number],

server_name,

database_name,

backup_set_id,

backup_finish_date,

last_lsn,

media_set_id

FROM msdb.dbo.[backupset]

WHERE [type] = 'L'

AND [database_name] = 'AdventureWorks'

)

SELECT TOP 10

FB.server_name,

FB.database_name,

FB.backup_finish_date,

BMF.physical_device_name AS primary_database_physical_device_name,

'RESTORE LOG [' + FB.database_name + '] FROM DISK = N''' + PD.backup_share + RIGHT(BMF.physical_device_name, LEN(BMF.physical_device_name) - LEN(PD.backup_directory)) + ''' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10' AS restore_command_for_log_shipped_copy

FROM full_backups FB

INNER JOIN msdb.dbo.backupmediafamily BMF ON FB.media_set_id = BMF.media_set_id

INNER JOIN msdb.dbo.log_shipping_primary_databases PD ON FB.database_name = PD.primary_database

WHERE FB.backup_finish_date > '2017-05-22 14:38:35.950' -----Here put the time when the last restore is done

ORDER BY FB.[Row Number] DESC;

The output will be like

RESTORE LOG [AdventureWorks] FROM DISK = N'\\machinename\Log-Shipping-Bkp-SQL001-to-SQL002\AdventureWorks_20170522051502.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [AdventureWorks] FROM DISK = N'\\machinename\Log-Shipping-Bkp-SQL001-to-SQL002\AdventureWorks_20170522053001.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

You also can find out the log file name and location prior to any given LSN or backup_start_date:

use msdb

select s.backup_set_id,

s.first_lsn,

s.last_lsn,

s.database_name,

s.backup_start_date,

s.backup_finish_date,

s.type,

f.physical_device_name

from backupset s join backupmediafamily f

on s.media_set_id = f.media_set_id

where s.last_lsn = '65000000027200001'

-- or backup_start_date < 'date'

and s.database_name = 'AdventureWorks'

order by s.backup_finish_date

You can check the last status from the Primary DB:

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

You can check the status from the Secondary DB:

SELECT last_copied_file

, last_restored_file

, last_copied_date

, last_restored_date

, last_restored_latency

FROM msdb.dbo.log_shipping_monitor_secondary

WHERE secondary_database = 'AdventureWorks';

Another way could be restart the whole logshipping process:

To remove and reinstate log shipping

Connect to the instance of SQL Server that is currently the log shipping primary server and expand that instance.
Expand Databases, right-click the log shipping primary database, and then click Properties.
Under Select a page, click Transaction Log Shipping. Take the screenshots of current configuration.
Clear the Enable this as a primary database in a log shipping configuration check box.
Click OK to remove log shipping from this primary database.
On the log shipping primary server, disable the backup job. For more information, see Disable or Enable a Job.

7. On the log shipping secondary server, disable the copy and restore jobs.

8. Backup the DB from Primary Site and restore it in Secondary Database:

RESTORE DATABASE [AdventureWorks] FROM Disk = 'C:\SQL-Backup\Log-Shipping-Bkp-SQL001-to-SQL002\AdventureWorks.bak' WITH NORECOVERY;

9. Then go to the Primary DB again and configure the Logshipping and configure the secondary DB.

10. Run the LSBackup from Primary manually and then LSCopy and LSrestore respectively from the secondary DB.

11. Logshipping should be fine now.

-----END-----

Courtesy:

https://blogs.technet.microsoft.com/mdegre/2009/08/08/logshipping-secondary-server-is-out-of-sync-and-lsrestore-job-failing/

https://blog.sqlauthority.com/2015/10/20/sql-server-log-shipping-restore-job-error-the-file-is-too-recent-to-apply-to-the-secondary-database/

https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/remove-log-shipping-sql-server

https://www.sqlservercentral.com/Forums/1669516/how-to-find-which-backup-file-belongs-to-which-LSN

Search This Blog

DBA-Philiac