Article ID: 292586 - View products that this article applies to.
This article was previously published under Q292586
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:
Failure to update msdb..log_shipping_secondaries on the monitor server occurs when the following conditions are met:
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:
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:
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/917544/ )BUG: You receive an error message when you run the "Log Shipping Alert Job - Restore" job in SQL Server 2000
Article ID: 292586 - Last Review: February 22, 2007 - Revision: 3.4