Sql Server Logshipping - Database falling into "in-Recovery" everytime.

Question

Hi This is Syed JB.

Actually I have large database which is involved in Transactional LogShipping as a Secondary(DR) server.

Issue – actual issue was, in recently I have observed that, database falling in “In-Recovery” mode when log(trn) files restore initiated/in progress. when I check while restore, log restore completed but it’s stuck on 100% completion-Restore NOT finished and relevant .TUF file not generated for a long time.
It was stuck of long time and once .TUF file started creating, while .TUF file started generating meanwhile my standby database falling in “In-Recovery” mode, parallelly .TUF file progressing(DB recovering and TUF file progressing). This is what happing frequently.

My findings was and as workaround - , .TUF file location/directly attributes set to Read-Only frequently – for this I have created a batch file job to remove read-only attributes and set to Read-Write to all for every-10Seconds. It was supported for a long time. But now the above issue repeating again, but not on daily, happening for every 02 days and some days daily.

• SQL server and OS Details - Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor).
• Environment – AWS EC2 Instance(IaaS).
• Sync type – Transactional LogShipping
• Partner DB mode for LS – Read-Only / StandBy
• Database size – 5.60TB

Please suggest for find out any other findings.

Regards,
Syed Jakeer,

Answer 1

But that's how log shipping works.
To restore further log backups the database must stay in recovery mode. On failover you restore the latest log backup with option RECOVERY and switch so the database to multi-user mode.
See https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/fail-over-to-a-log-shipping-secondary-sql-server?view=sql-server-ver15

Answer 2

Hi @Syed Jakeer ,

The secondary database in log shipping be in either the RECOVERING state or the STANDBY state, which leaves the database available for limited read-only access. The log backups from primary will be applied to secondary database with no recovery to make sure the subsequent log backups can be applied normally. So secondary database in recovery mode is normal.

Refer to MS document About Log Shipping (SQL Server).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".