SQL Server Log Shipping – Troubleshooting Out of Sync Issues

log-shippingsql server

I've configured a log shipping from a server, to the same server, only with a different instance.

Primary server is configured this way:

LSBACKUP_MyDatabase – every 25min

Secondary server:

LSCOPY_MyDatabase – every 1 minute

LSRESTORE_MyDatabase – every 10 minutes

Whats happening is, the primary backup job is running fine ( there is a lot more history, im just showing the last 2 ).

enter image description here

in the folder, I can see the TRN files.

enter image description here

In the secondary instance, LSCOPY and LSRESTORE is ok too. It's copying files, but the problem is here. The restore job is reporting this "message" ( the job runs succesfully, so I don't think it's a error):

Message

2015-12-29 09:10:02.41 Skipped log backup file. Secondary DB:
'MyDatabase', File: '\ServerIP\instancia
g\BACKUP\Log_Sp_Secundario\MyDatabase_20151229104500.trn'

2015-12-29 09:10:02.41 Could not find a log backup file that could be
applied to secondary database 'MyDatabase'.

2015-12-29 09:10:02.42 The restore operation was successful. Secondary
Database: 'MyDatabase', Number of log backup files restored: 0

2015-12-29 09:10:02.42 Deleting old log backup files. Primary
Database: 'MyDatabase'

2015-12-29 09:10:02.42 The restore operation was successful. Secondary
ID: '5a0a361c-039c-40a3-9c39-af5e338c7f72'

And then, when I click to see the history of the LSALERT_ Job, its reporting errors with this message:

enter image description here

Message Executed as user: CMDO\gdladmin. The log shipping secondary
database VMWGDLPRD04\GDLIC2014.GDL_IC has restore threshold of 45
minutes and is out of sync. No restore was performed for 8323 minutes.
Restored latency is 0 minutes. Check agent log and logshipping monitor
information. [SQLSTATE 42000] (Error 14421). The step failed.

According to one of the Microsoft's support pages, there is this query to show if there are gaps between logs. there are none:

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 = 'MyDatabase')
ORDER BY 
    s.backup_finish_date DESC;

I've been searching all over the internet, but I could only find those dba blogs with lack of information, and some posts saying that the primary database was dropped ( obviously was not ).

The primary instance is 2012. The secondary is 2014. the secondary database is in recovering mode.

To fix this, should I recreate all log shipping?

Best Answer

Well it didn't fixed. I think I will recreate the log shipping again

Before , you give a try with that, why don't you go for looking the most recent differential backup and restore it on the secondary.

We also had situations as mentioned above and found that:

This happened due to NW glitch due to which the folder shared(on primary as common backup location with secondary) was no more shared ( due to some issues on cluster resource) and as a result few log backups never went/copied to secondary and since there was a gap, even though restore job completed but LS kept saying out of sync.

Well, in our case we went ahead and restored the latest full back up to bring the missing LSN chains in sync and later the restore job picked the next log backup file and LS was back in sync.

Related Question