DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 298743 - Last Review: October 24, 2012 - Revision: 3.0

This article was previously published under Q298743
BUG #: 353349 (SHILOH_BUGS)
BUG #: 353719 (SHILOH_BUGS)

Symptoms

When you set up log shipping through the Database Maintenance Wizard, and the wizard fails to complete because of an error, not all of the wizard's changes are rolled back.

This may cause the following error message to occur after you fix the original problem, and then re-run the wizard:
Error 14261: The specified primary_server_name.primary_database_name ('N') already exists.
Error 14426: A log shipping monitor is already defined (...)

Cause

There are left over jobs on the log shipping monitor server and on the primary server:
  • Primary server: An entry for the monitor server in table msdb.dbo.log_shipping_monitor.
  • Monitor server: Job "Log shipping Alert Job - Backup".
  • Monitor server: Job "Log shipping Alert Job - Restore".
  • Monitor server: An entry for the primary server in table msdb.dbo.log_shipping_primaries.
The jobs are there because there are rows in the log_shipping_primaries table. In all failure cases of the setup Wizard the problem is that the log_shipping_primaries and log_shipping_monitor tables are not always cleaned up.

Workaround

After you run the Maintenance Wizard and it fails to create the Log Shipping setup, you must manually clean up the tables before you run the wizard again.
Depending on where the failure is, you must run some of the stored procedures to delete the entry.

You may have to run the following stored procedures to clean up log shipping entries and allow the wizard to be re-run:
  • sp_delete_log_shipping_primary (http://msdn.microsoft.com/en-us/library/aa259637(SQL.80).aspx) : To delete the primary server from the log_shipping_primary table.
  • sp_delete_log_shipping_plan (http://msdn.microsoft.com/en-us/library/aa933266(SQL.80).aspx) : To delete the log shipping plan.
  • sp_delete_log_shipping_secondary (http://msdn.microsoft.com/en-us/library/aa933265(SQL.80).aspx) : To remove secondary server from log_shipping_secondaries table.
  • sp_remove_log_shipping_monitor (http://msdn.microsoft.com/en-us/library/aa238873(SQL.80).aspx) : To delete the log shipping monitor information from the log_shipping_monitor table.

Status

Microsoft has confirmed this to be a problem in SQL Server 2000.

References

For more information, visit the following Microsoft Web sites:

Microsoft SQL Server 2000 Resource Kit (http://www.microsoft.com/mspress/books/toc/4939.aspx) ; Chapter 13


SQL Server 2000 Books Online (http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b)

Applies to
  • Microsoft SQL Server 2000 Enterprise Edition
Keywords: 
kbbug kbfaq kbpending kbsqlservlogship KB298743
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support