I am shipping transaction logs to another database that we will be using for certain reports that don't need real-time data. This works fine until we start directing traffic to it, then it lasts for a day or so and then just gets stuck in a Restoring state.
I am shipping transaction logs every 15 minutes from the production server and on the standby server, I have it set to to Standby mode with 'Disconnect users in the database when restoring backups' set to true (if I don't do this, the restore will be put off sometimes by a day or so, I am ok with killing the active sessions). Also on the standby server I have it set to run LSRestore every 10 minutes.
The problem is that I don't know what is causing the database to hang, nor do I know where I can even look to get some diagnostics that may tell me something.
Does anyone know where I can look?
It sounds like the Secondary cannot keep up with the processing of the restores of the t-log backups on the secondary. I would try decreasing the time of the backups, copies, and restores and see if you can find an optimal time interval where the secondary database does not stay in the restoring mode. I see you already have it down to every 15 minutes. Also, check this article and use the t-sql in it to dive down deep and try and see where the delays are occurring in more detail - https://www.sqlshack.com/monitor-transaction-log-shipping-using-t-sql-and-ssms/. I hope this helps.