O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

19.6. Monitoring and Troubleshooting

Log shipping has monitoring capabilities to identify the progress of the backup, copy, and restore jobs. For example, a job that has not made any progress is likely an indication that something is wrong and needs further analysis. If one of the jobs has failed, you can go to the SQL Agent job history and the Windows Event Viewer to identify the error message and correct it. Monitoring also helps to determine whether the backup, copy, or restore jobs are out of sync with the secondary server.

There are two approaches to monitoring the log-shipping process: using the Transaction Log Shipping Status report or executing the master.dbo.sp_help_log_shipping_monitor stored procedure. Either method can help you determine whether the secondary server is out of sync with the primary server, and the time delta between the two. You can also determine which jobs are not making any progress and the last transaction-log backup, copy and, restore file name processed on the secondary server.

Log shipping also alerts jobs that check if a preset threshold has been exceeded by executing the sys.sp_check_log_shipping_monitor_alert stored procedure. If the threshold has been exceeded, the stored procedure raises an alert. You can choose to modify the log-shipping alert jobs to capture the alert and notify you using SQL Agent.

If a monitor server is deployed as part of log shipping, there will be one alert on the monitor server that reports on the transaction-log ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required