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 ...
Get Professional Microsoft® SQL Server® 2008 Administration now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
