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:
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 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.
-- 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.
What are the other problems you have seen with Log-shipping? If you can share some of the common errors, it would be of great help for others and I will try to blog about them too with your help.
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
Log shipping restore failing for following databases and getting errors,
MSSQLSERVER 14421 Server The log shipping secondary database DR-CMSSQL01.WSS_Content has restore threshold of 120 minutes and is out of sync. No restore was performed for 192935 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
MSSQL$AECCRRP 14421 Server The log shipping secondary database DR-CMSSQL01\AECCRRP.Test2 has restore threshold of 45 minutes and is out of sync. No restore was performed for 81958 minutes. Restored latency is 15 minutes. Check agent log and logshipping monitor information.
MSSQL$AECCRRP 14421 Server The log shipping secondary database DR-CMSSQL01\AECCRRP.CRMPRD1_MSCRM has restore threshold of 120 minutes and is out of sync. No restore was performed for 5608 minutes. Restored latency is 60 minutes. Check agent log and logshipping monitor information.
MSSQL$AECDRP 14421 Server The log shipping secondary database DR-CMSSQL01\AECDRP.AECA_DWHDB has restore threshold of 120 minutes and is out of sync. No restore was performed for 86488 minutes. Restored latency is 60 minutes. Check agent log and logshipping monitor information.
MSSQL$AECCRRP 14420 Server The log shipping primary database DR-CMSSQL01\AECCRRP.Crmdev_MSCRM has backup threshold of 60 minutes and has not performed a backup log operation for 81943 minutes. Check agent log and logshipping monitor information.
SQL Log Error,
12/01/2015 09:16:50,Logon,Unknown,Login failed for user ‘AECA\fetdadmin’. Reason: Failed to open the explicitly specified database ‘GENGPD_model_model’. [CLIENT: 192.40.41.15]
Please advise,
Wanted to leave you a Thank You for this article and script. This helped me to solve my mystery……
Thanks for the comment TD.
Worked perfectly and resolved the issue.. Thank you very much
Thanks.. you are suppose to use your name.. test!
Hi,
I am facing below error oftenly in logshipping
Error:
2017-08-14 11:40:10.87 *** Error: Could not apply log backup file ‘\\AWSAUSDB01P\h$\Logshipping\moodle251\moodle251_20170814103001.trn’ to secondary database ‘moodle251’.(Microsoft.SqlServer.Management.LogShipping) ***
2017-08-14 11:40:10.87 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
While configuring logshipping i selected check box of disconned users also.
Also I have changed scheduled time for copy job every 15m and restored job every 10minutes of interval.
I checked everything on server level and db level. But i am not able to find the problem.
DB : Standby/read-only mode.
I have checked user transaction with the help of profiler on that particular time when job is getting failed but no luck.
Please help on this issue.
Thank you in advance.
HI,
Thank you for this.
I am facing the same error but i cannot locate the suspect .trn backup(due to the backup clean up job)is there a way to fix this without reconfiguring LS?
Thanks in advance.
Thanks for the article – that helped identify the oddball backup! FYI for others who may be having this problem on a virtual server, VMWare (Veeam) backups done with application-awareness turned on create a SQL backup with physical_device_name = {guid} and – by default – truncate transaction logs.
The provided query gives blank result.
why?
Not sure. It works for me and many.
Are you taking backups?
odd backup showing NULL in row… what to do ?
Ok, so check this out. I had been bashing my head against the wall on why my log shipping was failing. So I ran your query above and it showed me that I had a tlog job happening with a location of our blob resource. Azure apparently was doing automagic backups that weren’t part of the normal sql server management processes but rather managed within Azure. These were creating full backups but empty tlog files which disrupted the tlog shipping sequence by incrementing the process with empty tlogs. Disabled the Azure SQL backup and log shipping is working now.