Hi SQL Server Expert,,

Currently we have 2 Server, one in house and the other one is on the cloud. these two database server is actually connected via VPN and there is log shipping configured. so, the replication will be from the production database server to the cloud. Both server is using Server 2012 Standard and SQL Server 2012 too.

2 days ago, one of the engineer perform the log file shrinking on the primary server (in house database server) and the restore job on the log shipping now is not working. The backup and copy job are still running perfectly, just when come to the restore job it is not running anything. From the log I can only set that it set the database to Single user and then come with errors, hence no restore is being performed.

I am not a DB admin and have no knowledge about how log shipping work. My thinking is maybe because due to the shrinking of log files then the restore job is always fail. will this the cause of the issue?

Thanks.

Regards,

H

Normal shrinking would never break a log chain. But if you shrink with NO_LOG or truncate_only this can break logshipping. If you use NO _TRUNCATE while shrinking log file it wont affect log chain. I am sure either he used options which I have mentioned or someone initiated manual log backup which led LSN chain to break.

Can you show me error which is coming when restore job is failing ?

Normal shrinking would never break a log chain. But if you shrink with NO_LOG or truncate_only this can break logshipping. If you use NO _TRUNCATE while shrinking log file it wont affect log chain. I am sure either he used options which I have mentioned or someone initiated manual log backup which led LSN chain to break.

Can you show me error which is coming when restore job is fai

Normal shrinking would never break a log chain. But if you shrink with NO_LOG or truncate_only this can break logshipping. If you use NO _TRUNCATE while shrinking log file it wont affect log chain. I am sure either he used options which I have mentioned or someone initiated manual log backup which led LSN chain to break.

Can you show me error which is coming when restore job is fai

Normal shrinking would never break a log chain. But if you shrink with NO_LOG or truncate_only this can break logshipping. If you use NO _TRUNCATE while shrinking log file it wont affect log chain. I am sure either he used options which I have mentioned or someone initiated manual log backup which led LSN chain to break.

Can you show me error which is coming when restore job is fai

Can you reinitialize this log shipped database? Shrinking the log via management studio will not break log shipping. Something else must have

Yes I can see the message says that log backup you are trying to restore is too recent to apply.

As mentioned by Hilary as a last resort you can take full backup from primary and restore it on secondary and then if required take log backup (just after the full backup) and restore it on secondary to recreate log shipping.

Yes, Shanky is correct, I missed that! You need to take a full backup, send it to the secondary, restore it with the norecovery switch and then your log shipping will pick up where it left off.

Yes I can see the message says that log backup you are trying to restore is too recent to apply.

As mentioned by Hilary as a last resort you can take full backup from primary and restore it on secondary and then if required take log backup (just after the full backup) and restore it on secondary to recreate log shi

Ok I can understand your dilemma . Unfortunatly only step without reinitializing is you find out what transaction log is missing and is not been restored and restore it. If you are able to do that automatically restore job would take care but this would be little tough for beginner like you that is why we suggested to reinitialize.

If you want so reinitialize you can refer to below link

https://www.mssqltips.com/sql-server-tip-category/100/log-shipping/

do let me know if you have any further concerns

Ok I can understand your dilemma . Unfortunatly only step without reinitializing is you find out what transaction log is missing and is not been restored and restore it. If you are able to do that automatically restore job would take care but this would be little tough for beginner like you that is why we suggested to reinitialize.

If you want so reinitialize you can refer to below link

https://www.mssqltips.com/sql-server-tip-category/100/log-shipping/

do let me know if you have any further concerns

 I think the SQL will maintain the indexing during the log shipping. So by theory, the sequence should not be hail wire right? meaning if a transaction log called "A" is not being restored, then the transaction log "B" can't be restored, etc...

No this is not completely correct if somehow you miss one transaction log backup  the restore sequence will not proceed unless that log backup is restored , this is what is precisely happeing with you

Please. I need your advise as it has been four days my log shipping failing for one of the database. I am worry that there is a need to do the whole thing again as this has been setup by previous guy.

Thanks.

You can run below queries on Log_shipping_monitor_primary on Primary server

select * from mdb.dbo.log_shipping_monitor_primry

and below on secondary server

select * from msdb.dbo.log_shipping_monitor_secondary

From first query see what is last_backup_file and Last_backup_date

From second query see last_restored_file and then you can figure out what next log file you have to restore and then you can restore it manually in standby

 I think the SQL will maintain the indexing during the log shipping. So by theory, the sequence should not be hail wire right? meaning if a transaction log called "A" is not being restored, then the transaction log "B" can't be restored, etc...

No this is not completely correct if somehow you miss one transaction log backup  the restore sequence will not proceed unless that log backup is restored , this is what is precisely happeing with you

Please. I need your advise as it has been four days my log shipping failing for one of the database. I am worry that there is a need to do the whole thing again as this has been setup by previous guy.

Thanks.

You can run below queries on Log_shipping_monitor_primary on Primary server

select * from mdb.dbo.log_shipping_monitor_primry

and below on secondary server

select * from msdb.dbo.log_shipping_monitor_secondary

From first query see what is last_backup_file and Last_backup_date

From second query see last_restored_file and then you can figure out what next log file you have to restore and then you can restore it manually in standby

 I think the SQL will maintain the indexing during the log shipping. So by theory, the sequence should not be hail wire right? meaning if a transaction log called "A" is not being restored, then the transaction log "B" can't be restored, etc...

No this is not completely correct if somehow you miss one transaction log backup  the restore sequence will not proceed unless that log backup is restored , this is what is precisely happeing with you

Please. I need your advise as it has been four days my log shipping failing for one of the database. I am worry that there is a need to do the whole thing again as this has been setup by previous guy.

Thanks.

You can run below queries on Log_shipping_monitor_primary on Primary server

select * from mdb.dbo.log_shipping_monitor_primry

and below on secondary server

select * from msdb.dbo.log_shipping_monitor_secondary

From first query see what is last_backup_file and Last_backup_date

From second query see last_restored_file and then you can figure out what next log file you have to restore and then you can restore it manually in standby