SQL Server Performance

Log Shipping & Loading status

Discussion in 'SQL Server Log Shipping' started by chakra, Aug 2, 2005.

  1. chakra New Member

    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.
  2. satya Moderator

    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.
  3. chakra New Member

    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.
  4. satya Moderator

    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.
  5. chakra New Member

    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.

Share This Page