Log Shipping principal taken offline, how to stop witness messages?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » Log Shipping principal taken offline, how to...

Log Shipping principal taken offline, how to stop witness messages?

Rate Topic

Display Mode

Topic Options

Author

Message

rpp6182

Posted Monday, October 15, 2012 9:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 25, 2013 1:21 PM
Points: 4, Visits: 12

I have taken the primary server in a log shipping pair offline permantly; the machine has been re-purposed. I thought I had stopped log shipping quite a while back as this machine has not been production for some months and the secondary, though not renamed, has been happily being used for some testing for the same application.

However, beginning about an hour after taking the machine down, the witness is generating messages for 4 databases barking about no backup within the threshold time.

How can I turn off these warnings now that the principal machine is no longer available?

Post #1372785

rpp6182

Posted Monday, October 15, 2012 9:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 25, 2013 1:21 PM
Points: 4, Visits: 12

Oh, a couple of more details.

The witness machine is also the witness for several other log shipping machines and a mirror. The messages produced are:

DESCRIPTION: The log shipping primary database [ServerName].DBName] has backup threshold of 60 minutes and has not performed a backup log operation for 75 minutes. Check agent log and logshipping monitor information.

The job failed with this message in the SQL Agent log:

Executed as user: [domain\ServiceAccount]. The log shipping primary database [ServerName].DBName] has backup threshold of 60 minutes and has not performed a backup log operation for 75 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14420). The step failed.

Thank you in advance for your help. Smile

Post #1372789

rpp6182

Posted Monday, October 15, 2012 11:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 25, 2013 1:21 PM
Points: 4, Visits: 12

Okay, I figured out the problem.

First, I got a couple of things wrong. My engineering team took BOTH the old primary and the old secondary machine down. The secondary machine was being used as a secondary machine in the test environment to test fail-over procedures. So the real situation was I had a primary and a witness that had lost the secondary machine.

The solution to stop the flood of alert message was a bit involved. I tried using the GUI to remove log shipping, but that failed since it could not talk to the secondary server. Ultimately, I had to do some behind the curtains work.

First, on the primary I had to run the following script for EACH database that had been log shipped.:

USE master
GO

EXEC sys.sp_delete_log_shipping_primary_secondary
@primary_database = LogShipDB, -- sysname
@secondary_server = SecondaryServer, -- sysname
@secondary_database = LogShipDB -- sysname*/
GO

EXEC sys.sp_delete_log_shipping_primary_database
@database = LogShipDB, -- sysname
@ignoreremotemonitor = NULL -- bit
GO

After this I had to run this command on the Witness machine. This was a bit scary as I was going straight after systems tables without using the GUI or any stored procedures.

USE msdb
GO

delete FROM log_shipping_monitor_secondary
WHERE secondary_server = 'vmaxBirmSQL1'
GO

Note that on the Primary machine, the queries had to be run from the master database but on the Witness they had to be run from msdb.

Yuck. What a mess. Glad I got it figured out.

Post #1372853

Perry Whittle

Posted Monday, October 15, 2012 11:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:15 PM
Points: 5,204, Visits: 11,162

remove traces of any redundant secondary databases on the secondary server using

USE master;
go
exec sp_delete_log_shipping_secondary_database 
@secondary_database = 'secondary_database';

Once you're sure any LS scenarios have been cleared remove the alert job using

USE master;
GO
EXEC sp_delete_log_shipping_alert_job;

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1372854

rpp6182

Posted Monday, October 15, 2012 11:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 25, 2013 1:21 PM
Points: 4, Visits: 12

Thank you. I forgot to mention that last step. Since the secondary server has been wiped, there I do not need to run that cleanup there. Thank you, however. Smile

Post #1372862

« Prev Topic | Next Topic »

Permissions