SQL Server Performance

monitoring log shipping matrics

Discussion in 'SQL Server Log Shipping' started by nikhiljain, Dec 1, 2005.

  1. nikhiljain New Member

    Hi all,
    1)I have a question regarding the architectural behaviour of Log shipping. Like which is medium used for storing the information about log shipping like its a log file or system table of msdb.
    2) Suppoese i made one plan for log shipping and schedule it for "after 20 min daily".
    Now i want to know that in each schedule how much time it will take to do the compelete operation and how much data copied/reload while performing log shipping operation.
    3) If my log shipping fails after running 3 times successfully. from where i'll get the information about the log shipping fails.

    Since i want to monitor the log shipping matrics and also want to know about the failure information.

    Thanks in advance,
    Nikhil Kumar Jain
  2. SQLDBcontrol New Member

    Hi Nikhil,

    Most of the information you need regarding log shipping is contained on several log shipping tables on the secondary server.

    The primary server just has the maintenance plan for the backup job and a pointer to the log shipping secondary database.

    Have a look at the following tables (this answers question 1 and 3)

    log_shipping_monitor
    log_shipping_databases
    log_shipping_plans
    log_shipping_plan_databases
    log_shipping_plan_history
    log_shipping_primaries
    log_shipping_secondaries


    Log shipping is pretty simple really. A backup job runs on the primary server and backs up the log. At the same time it sends a notification to the monitoring (or secondary, can't remember) that the backup has completed.

    On the seconday, a log copy job copies logs from the specified location on the primary to the specified location on the seconday (this info is contained in the log_shipping_plans and log_shipping_primaries/secondaries tables I believe).

    Then another job, restores the copied logs.

    The log_shipping_plans_history table helps log shipping keep track of what has been copied and restored so that it knows what to restore next.

    As for question 2, there is no way of know how long the process takes. It depends on how many transactions run in between each backup. The bigger the log the longer the process will take.

    Hope this helps.



    quote:Originally posted by nikhiljain

    Hi all,
    1)I have a question regarding the architectural behaviour of Log shipping. Like which is medium used for storing the information about log shipping like its a log file or system table of msdb.
    2) Suppoese i made one plan for log shipping and schedule it for "after 20 min daily".
    Now i want to know that in each schedule how much time it will take to do the compelete operation and how much data copied/reload while performing log shipping operation.
    3) If my log shipping fails after running 3 times successfully. from where i'll get the information about the log shipping fails.

    Since i want to monitor the log shipping matrics and also want to know about the failure information.

    Thanks in advance,
    Nikhil Kumar Jain


    Karl Grambow

    www.sqldbcontrol.com

Share This Page