Backup, copy, and load job information is not updated on the log shipping monitor

When you view a log shipping monitor for a log shipping pair, the Last file copied and Last file loaded values may continuously show "first_file_000000000000.trn", even though the copy and load jobs on the secondary server are executing correctly without any errors. If the values remain the same over an extended period of time, log shipping thresholds could be crossed and alerts could be fired.

When you use a monitor server that is different from the primary server, the Last backup file value on the log shipping monitor may also show "first_file_000000000000.trn" (or some other file name) over an extended period of time, even though the backup jobs are running fine.

NOTE: In SQL Server 2000 log shipping terminology, a monitor server is a server that keeps track of the files which are backed up on a primary server and then copied and loaded onto the secondary server. You specify the server that will monitor a log shipping pair during the log shipping setup for a database.

This behavior is seen when the msdb..log_shipping_primaries table on the monitor server is not updated during backup and/or the msdb..log_shipping_secondaries table is not updated with every copy/load of a transaction log.

Failure to update msdb..log_shipping_primaries on the monitor server occurs when the following conditions are met:

• The monitor server is an instance other than the primary SQL Server instance.
• The Use Windows Authentication option is selected on the Specify The Log Shipping Monitor Server Information screen during log shipping setup.
• The SQL Server Service startup account on the primary server of a log shipping pair does not have sufficient privileges to update msdb..log_shipping_primaries on the monitor server.
• The value in primary_server_name column in log_shipping_primaries does not match the @@servername of the primary server.

When these conditions are met, the log shipping monitor will continuously display the Last backup file value as "first_file_000000000000.trn".

Failure to update msdb..log_shipping_secondaries on the monitor server occurs when the following conditions are met:

• The monitor server is an instance other than the secondary SQL Server instance.
• The Use Windows Authentication option is selected on the Specify The Log Shipping Monitor Server Information screen during log shipping setup.
• The SQL Server Service startup account on the secondary server of a log shipping pair does not have sufficient privileges to update msdb..log_shipping_secondaries on the monitor server.
• The value in secondary_server_name column in log_shipping_secondaries does not match the @@servername of the secondary server.

When these conditions are met, the log shipping monitor will continuously display the Last file copied and Last file loaded values as "first_file_000000000000.trn".

Both of these problems could also be caused if SQL Server authentication is selected for the log shipping monitor and the log_shipping_monitor_probe login privileges on the monitor server's msdb database are changed after log shipping setup.

Make sure that the SQL Server Service startup account for the primary server (if the Last backup file value is not being updated) and secondary server (if the Last file copied and Last file loaded values are not being updated) have sufficient privileges to update tables in the msdb database on the monitor server. Depending on which values in the log shipping monitor tool are not being updated, the SQL Server Service startup account of the primary/secondary server should be added to the db role of db_owner on msdb or the System Administrator server role for the log shipping monitor to work as intended.

To check or modify permissions on the log_shipping_primaries and log_shipping_secondaries tables on the server designated as the monitor server in the log shipping pair, follow these steps:

1. In SQL Enterprise Manager, select the designated monitor server.
2. Click the plus sign (+) next to Databases to open the databases folder.
3. Open the msdb database.
4. Click the tables icon under msdb.
5. On the right pane of the Enterprise Manager window, double-click the log_shipping_primaries table.
6. Click the Permissions button.
7. Select the Update and Select check boxes for the required users; in this case, the SQL Server Startup account on the primary server for the log shipping pair.
8. Repeat steps 1 to 7 but in step 5 double-click the log_shipping_secondaries table.

Consider the following scenario:

• Server P is the log shipping primary server.
• Server S is the log shipping secondary server.
• Server M is the log shipping monitor server.

The log shipping monitor tool will be provided on Server M. In order for this tool to display the correct information, the following conditions must be met:

• When using Windows NT authentication for the log shipping monitor, the SQL Server service startup accounts on Server P and Server S should at least have Select and Update privileges on the log_shipping_primaries and log_shipping_secondaries tables in the monitor server's msdb database.
• When using SQL Server authentication for the log shipping monitor, make sure that the default permissions set for the log_shipping_monitor_probe login are not changed on Server M. By default, the login will be added to the public role on the msdb of Server M with Select and Update privileges on the msdb..log_shipping_primaries and msdb..log_shipping_secondaries tables.

The log_shipping_primaries and the log_shipping_secondaries tables are updated based on the SQL Server server names. You must run on both primary and secondary servers. Then, verify that the values in the primary_server_name and the secondary_server_name columns of the tables in the Monitor server match the @@servername values respectively.

Log shipping uses the functionality provided in Sqlmaint.exe to back up and restore databases with various options. Whenever a transaction log backup is made as part of a log shipping setup, Sqlmaint.exe connects to the monitor server and updates log_shipping_primaries with the last_backup_filename information. Similarly, when a copy/restore job runs on a secondary server, Sqlmaint.exe on the secondary server should connect to the monitor server and update the log_shipping_secondaries table.

The connection type depends on the selection made for authentication on the Specify The Log Shipping Monitor Server Information screen during log shipping setup. If Use Windows Authentication is selected, the connection is made using the SQL Server startup account of the primary/secondary server. If SQL Server Authentication is selected, the connection is made using the log_shipping_monitor_probe login that is created on the monitor server during log shipping setup.

Any SQL Server edition can be used as a monitor server. The tables required to set up monitor server are included with all editions of SQL Server 2000.

The two tables from which the log shipping monitor information is retrieved are:

• Log_shipping_primaries: This table stores information about the backups done on the primary server, including the last backup file that was created by the maintenance plan. It also contains the threshold value (in minutes) after which alerts are raised. Information about when to suppress alerts can be defined by using the log shipping monitor interface to account for any planned outages when the server will not be active. The information to suppress these alerts will also be stored in the log_shipping_primaries table.
• Log_shipping_secondaries: This table stores information about the last file copied to the secondary server and the last file restored on the secondary server. These values are updated in the table with the execution of the log shipping copy and load jobs on the secondary server. Alert threshold and alert suppression information for the secondary server are stored in this table.

For more information, click the following article number to view the article in the Microsoft Knowledge Base: