Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log Shipping principal taken offline, how to stop witness messages? Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:32 AM
Points: 4, Visits: 14
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
Posted Monday, October 15, 2012 9:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:32 AM
Points: 4, Visits: 14
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.
Post #1372789
Posted Monday, October 15, 2012 11:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:32 AM
Points: 4, Visits: 14
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
Posted Monday, October 15, 2012 11:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 5,958, Visits: 12,839
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"
Post #1372854
Posted Monday, October 15, 2012 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 8:32 AM
Points: 4, Visits: 14
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.
Post #1372862
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse