SQL Server Performance Forum – Threads Archive

monitoring log shipping matrics

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

---

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.
Karl Grambow www.sqldbcontrol.com

---

]]>