question

Warren Wang avatar image
Warren Wang asked

Can not rebuild the log shipping after I delete the old log shipping jobs for a database!

Hi, I want to restart the log shipping from database A. I deleted the old log shipping jobs for database A from SQL Agent and when I right click database A and point to properties then point to Transaction log shipping, I got "The current log shipping configuration is not functional because the required backup job cannot be found. You must drop the current configuration by clearing the 'Enable this as a primary database in a log shipping configuration' checkbox. To resume log shipping, recreate the configuration using the same settings...." "Error Number: 14262 Severity: 16 State: 1 Procedure: sp_verify_job_identifiers Line Number: 32" Once I follow the instruction to uncheck and recheck th "Enable this as a primary database in a log shipping configuration", I am in the Transaction Log Backup Settings screen but I cannot setup the Backup job since the job name is blank and schedule is unknown schedule type. I cannot setup log shipping for database A any more unlesss I recover this backup job or delete it from the system (I believe it is still running in the msdb but I am not able to get rid of it??). I have rebooted my SQL server 2005 but I still have the same issue once I want to setup log shipping on database A. I know I should not delete the old log shipping jobs in the first place! Can someone tell me how to recover in my situation to rebuild the log shipping for database A??? Thank you.

log-shipping
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Warren Wang avatar image
Warren Wang answered

David, Thanks for the answer and it did provide great information for me to look at it. Actually, I did get this log shipping thing fixed through the following research and tests from the msdb database:

  • From the Primary Server - I did

select * from msdb.dbo.log_shipping_primaries
then
delete from msdb.dbo.log_shipping_primaries where primary_database='MyDatabase'

select * from msdb.dbo.log_shipping_monitor_primary
then
delete from msdb.dbo.log_shipping_monitor_primary where primary_database='MyDatabase'

select * from msdb.dbo.log_shipping_primary_databases
then
delete from msdb.dbo.log_shipping_primary_databases where primary_database='MyDatabase'

select * from msdb.dbo.log_shipping_primary_secondaries
then
delete from msdb.dbo.log_shipping_primary_secondaries where secondary_database='MyDatabase'

  • From the Secondary Server - I did

select * from msdb.dbo.log_shipping_secondary
then
delete from msdb.dbo.log_shipping_secondary where primary_database='MyDatabase'

select * from msdb.dbo.log_shipping_monitor_secondary
then
delete from msdb.dbo.log_shipping_monitor_secondary where primary_database='MyDatabase'

select * from msdb.dbo.log_shipping_secondary_databases
then
delete from msdb.dbo.log_shipping_secondary_databases where secondary_database='MyDatabase'

After I did this cleanup process for the three databases I deleted log shipping jobs earlier, I was able to rebuild log shipping process for all three databases again.

Lessons learned - never delete the log shipping jobs from SQL Agent. If you want to rebuild the log shipping, just uncheck the "Enable this as a primary database in a log shipping configuration" box in the Transaction Log Shipping properties under each database and let the system to get rid of the unwanted jobs!

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image David Wimbush commented ·
Glad you solved it. And thanks for posting the solution.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Warren - Thanks for coming back to post the solution, it really is appreciated. Hope you didn't mind me doing some formatting work on your answer...
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered

It sounds like it's got itself mixed up. I believe it's calling sp_verify_job_identifiers during the process of setting up the schedule for the backup job. Presumably there's something that hasn't been set up correctly. I would remove log shipping for this database and then make sure all jobs for it are definitely gone. Delete them if necessary. If it still won't go on, I'd look into the stored procs that are used to set up log shipping and look into the tables it updates to see if you can find out what's stuck. The procs to look at are:

master.dbo.sp_add_log_shipping_primary_database
msdb.dbo.sp_add_schedule
msdb.dbo.sp_attach_schedule
msdb.dbo.sp_update_job
master.dbo.sp_add_log_shipping_alert_job

I'd start with sp_add_schedule because I think this is the one that calls sp_verify_job_identifiers.

Good luck. And, if you fix it, please add an answer showing what you did so everyone can benefit.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.