SQL Server Performance Forum – Threads Archive

Log Shipping & Loading status

We have implemented Log shipping for our live databases. Of late, we notice that some of the databases in the secondary server often slip into "Loading" mode and remain as such for a very long time, after which we have to restore the DB from the full backup in the Primary Server. Any idea, why this happens? – Databases are relatively small (around 25 mb) and space is not the issue. – While the DB is in the "loading" status, log shipping appears to run well (Log Shipping Monitor says so). – Schema changes were NOT done in these DBs. Any help would be much appreciated.

If you use WITH NORECOVERY the secondary databases will go into loading state and only if you want to use secondary server for read-only MIS purpose then you can use WITH STANDBY clause between the log restores. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you Satya. I set up the log shipping using Maintenance Wizard and I had specified STAND BY as the load state. The database was in "Read Only" status until recently, but it went to "Loading" mode all of a sudden, which puzzles me.

Yes it is by default due to WITH NORECOVERY clause, stop Log shipping restore current backup and then restart LS with STANDBY clause. Also check whether any issues on secondary server where the database files are located. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks very much Satya. I dropped the secondary DB, recreated the maintenance plan with STANDBY clause, also made it take a full backup and restore the same in the secondary server. Its all fine now. Thanks a ton for your help. – Chakra from UK.

]]>