Redgate Log Shipping Monitor
- An introduction to Log Shipping Monitor. Log Shipping Monitor is absolutely free, and no sign-up is required, but there's also no technical support.
- Databases using the simple recovery model do not implement the transaction log in a manner compatible with log shipping and. Log Shipping Monitor from Redgate.
I've configured a log shipping from a server, to the same server, only with a different instance. Primary server is configured this way: LSBACKUPMyDatabase - every 25min Secondary server: LSCOPYMyDatabase - every 1 minute LSRESTOREMyDatabase - 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, 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.
Remove Log Shipping Monitor
Log Shipping Monitor by red-gate: The Log Shipping Monitor is a free tool for collating and summarising SQL Server Log Shipping activity.
Secondary DB: 'MyDatabase', File: ' ServerIP instancia g BACKUP LogSpSecundario MyDatabase4500.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.GDLIC 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.
Sql Server Log Shipping
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.databasename,s.backupfinishdate,y.physicaldevicename FROM msdb.backupset AS s INNER JOIN msdb.backupfile AS f ON f.backupsetid = s.backupsetid INNER JOIN msdb.backupmediaset AS m ON s.mediasetid = m.mediasetid INNER JOIN msdb.backupmediafamily AS y ON m.mediasetid = y.mediasetid WHERE (s.databasename = 'MyDatabase') ORDER BY s.backupfinishdate 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? 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. I take the LSAlert job with a grain of salt.
Troubleshooting that issue made me pull my hair out! In the end, the log backup, copy, and restore jobs were functioning as expected. Here are a few things to consider when you're getting that LSAlert error messages on your monitor server:.
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. When the monitor server is offline and got back online, the fields in the logshippingprimaries table are not updated with the current values before the alert message job runs. 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 logshippingprimaries table. This may be the result of an authentication problem between the monitor server and the primary server. The log shipping Restore job that is running on the secondary server cannot connect to the monitor server msdb database to update the logshippingsecondaries table with the correct value.
This may be the result of an authentication problem between the secondary server and the monitor server. 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.
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. There are couple of ways that you can verify that restores are happening as intended.