Knowledge Base

FIX: Log Shipping with SQL Server Authentication Generates Backup and "Out of Sync" Threshold Alerts

Article ID: 810969

Article Last Modified on 10/3/2003

APPLIES TO

Microsoft SQL Server 2000 Service Pack 2

SYMPTOMS

SQL Server may generate log shipping backup and "out of sync" threshold alerts even if log shipping functions correctly. For example, the following errors trigger alerts:

Error: 14420, Severity: 16, State: 1 The log shipping source %s.%s has not backed up for %s minutes.

Error: 14421, Severity: 16, State: 1 The log shipping destination %s.%s is out of sync by %s minutes.

You may see the following error in the maintenance plan report file if you specify the report file when you set up log shipping or when you manually run the xp_sqlmaint stored procedure from SQL Query Analyzer for the transaction log backup:

[Microsoft SQL-DMO (ODBC SQLState: 28000)] Error 18456: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'log_shipping_monitor_probe'.

You might experience these symptoms if you configured primary or secondary servers (or both) to connect to the monitor server by using SQL Server Authentication on a computer that is running SQL Server 2000 Service Pack 2 (SP2).

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

To work around this problem, click the Use Windows Authentication option in the Specify the Log Shipping Monitor Server Information dialog box during log shipping Setup.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

MORE INFORMATION

Errors occur even though the backup, copy, and load functions work correctly. Errors also occur if the Last_backup_filename and the Last_loaded_filename columns of the Log_shipping_primaries and the Log_shipping_secondaries tables on the monitor server are not updated correctly.

REFERENCES

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

292586 PRB: Backup, Copy, and Load Job Information Is Not Updated on the Log Shipping Monitor

314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping

For more information about how to configure log shipping (SQL Server Enterprise Manager), visit the following Microsoft Web site:
http://msdn.microsoft.com/library/en-us/howtosql/ht_servpem_8gtl.asp

Keywords: kbbug kbfix kbsqlserv2000sp3fix kbsqlservlogship KB810969

Microsoft KB Archive/810969

Contents