Microsoft SQL Server 2012 Bible

Monitoring Log Shipping

To monitor log shipping from SQL Server Management Studio, Microsoft provides a Transaction Log Shipping Status report. This report can be run on any SQL Server participating in the log shipping configuration. To run the Transaction Log Shipping Status report, execute the following steps:

1. Connect to the primary, secondary, or monitor server using Object Explorer in SQL Server Management Studio.

2. Right click the SQL Server instance; click Reports.

3. Click Standard Reports.

4. Click Transaction Log Shipping Status.

For a complete picture of the Log Shipping configuration, run this report on the monitor server, as shown in Figure 26.10.

Figure 26.10 Executing the Transaction Log Shipping Status report on monitor server.

Another method to monitor log shipping is to directly review the status of the log shipping jobs. You can review the history of the transaction log backup job on the primary server and the history of the copy and restore jobs on the secondary server.

Log shipping can also be monitored using several monitoring tables and stored procedures. The information that can be retrieved from these sources includes the database name, last backup, last restore, time since last restore, and whether the alerts are enabled.

The following is a list of the tables that can be used to monitor log shipping. These tables exist in the MSDB database (because ...