Monitoring and Troubleshooting

Log shipping has monitoring capabilities to identify the progress of the backup, copy, and restore jobs. For example, if a job has not made any progress, that can be 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. Also, monitoring helps to determine if the backup, copy, or restore jobs are out of sync with the standby 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 if the standby server is out of sync with the primary server and what is 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 filename processed on the standby server.

Additionally, log shipping 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 and notify you using SQL Agent.

Along with log shipping, if a monitor server is deployed, there will be one alert on the monitor server that manages the transaction-log backup, copy file, and restore transaction log. If not, the primary server will manage the alert job for the transaction-log backup, and the secondary server will manage the alert job for the copy file and restore transaction log. If the monitoring server is present, the primary and secondary servers will not deploy alert jobs.

The following is an example error if the transaction-log backup process has exceeded the preset threshold of 30 minutes:

 Executed as user: NT AUTHORITY\SYSTEM. The log shipping primary database SQLServer1.AdventureWorks has backup threshold of 30 minutes and has not performed a backup log operation for 60 minutes. Check agent log and log shipping monitor information. [SQLSTATE 42000](Error 14420).  The step failed. 

The following is an example error if the restore transaction-log process has exceeded the preset threshold of 30 minutes:

 Executed as user: NT AUTHORITY\SYSTEM. The log shipping secondary database SQLServer2.AdventureWorks has restore threshold of 30 minutes and is out of sync. No restore was performed for 60 minutes. Restored latency is 15 minutes. Check agent log and log shipping monitor information. [SQLSTATE 42000] (Error 14421). The step failed. 

As an example, you can set up an alert so that when error 14420 or 14221 is raised, SQL Agent sends an alert to the operator.

Monitoring with Management Studio

The Transaction Log Shipping Status report displays monitoring information from the Management Studio. The report executes the sp_help_log_shipping_monitor stored procedure. When executed on the primary server, it reports on the transaction-log backup details; when executed on the secondary server, it reports on the copy and transaction-log restore details. When the monitor server is configured, the report executed from the monitor server will produce a consolidated report of the transaction-log backup, copy, and transaction-log restore details in one report. To access the Transaction Log Shipping Status report:

1. Connect to the primary, secondary, or monitor server. The monitor server is the most useful choice, because it has the consolidated log-shipping detail data.

2. If the Object Explorer is not visible, select the ViewObject Explorer.

3. Choose the server node in the Object Explorer.

4. If the Summary page is not displayed, select the ViewSummary.

5. In the Summary page, select the Reports button and click the Transaction Log Shipping Status.

Figure 19-6 shows an example of the Transaction Log Shipping Status report executed from the monitor server, showing the details for all log-shipping activities with no alerts.

Figure 19-6

Figure 19-7 shows an example of the Transaction Log Shipping Status report executed from the monitor server, showing the details for all log-shipping activities with alerts.

Figure 19-7

Monitoring with Stored Procedures

Executing the sp_help_log_shipping_monitor stored procedure in the master database from a SQL query window will produce log-shipping status details, similar to the Transaction Log Shipping Status report. If you execute it from the primary server, it will return detailed information on the transaction-log backup job. If you execute it from the secondary server, it will return information on the copy and transaction-log restore jobs. If it is executed from the monitor server, it will return a consolidated detail result of the transaction-log backup, copy, and transaction-log restore, as the monitor server has visibility to all log-shipping processes.

For additional log-shipping operational and troubleshooting detail information, the log-shipping tables can be queried using the log-shipping stored procedures mostly found in the MDDB database. For more information see, the SQL Server 2005 Books Online.

Troubleshooting Approach

As we've mentioned, log shipping consists of three basic operations: Back up the transaction log, copy the file, and restore the transaction log. Troubleshooting this process is simply a matter of identifying which operation is not functioning. You can use the log-shipping monitoring capabilities to identify where the problem is. For example, if the restore transaction-log file is showing that no new files have been restored in the last 60 minutes, you need to look at the secondary server first.

Look at the log-shipping job history under the SQL Agent and the Windows Event Viewer to determine the actual error message. For example, if the copy file job is failing, it may be that the network is down. If the restore transaction-log job is failing, it may be that the server is unavailable or that users are using the database if the database is in standby mode.

Moreover, beware that if the database recovery model is changed to Simple, it will break log shipping because the transaction log is truncated instead of backed up. At that point, you would need to reconfigure log shipping. If you have saved the log-shipping configuration scripts, the reconfiguration should be fairly simple. Additionally, there should not be any other transaction-log backup operation outside of log shipping, because that will break log shipping, since then the log chain will not match on the standby server.