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
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 ).
in the folder, I can see the TRN files.
In the secondary instance,
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):
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:
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?
msdb.dbo.log_shipping_monitor_secondaryto get more information about transaction log file backed up, moved and restored
select * from msdb.dbo.log_shipping_monitor_secondary, in the ´last_restored_file` the value is null. I changed the log shipping settings to
25 minfor backup, copy and restore. I hope this will fix it.