In this post, I am sharing one T-SQL Script to identify the last restored Log backup file of the Secondary database in the SQL Server Log Shipping.
The one of the important points in Log Shipping is, both Primary Database and Secondary Database should be in sync.
If both are out of sync, SQL Server stops the Log Shipping and throws error related to log files.
As a SQL DBA, I am facing this kind of common errors of Log Shipping like:
“.trn is too recent to apply to the secondary database”
“Could not find a log backup file that could be applied to secondary database”
Using below T-SQL script, you can find last restored log backup in the Secondary Database which help you to compare with available log backups of Primary Database.
You can also easily find out any pending restore of log backup.
FROM [msdb].[dbo].[log_shipping_secondary] AS lss
INNER JOIN [msdb].[dbo].[log_shipping_secondary_databases] AS lssd
ON [lss].[primary_database] = [lssd].[secondary_database]
WHERE [lss].[primary_database] ='Primary_Database_Name'
ORDER BY [lss].[primary_database]
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.