SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
rpp6182
rpp6182
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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?
rpp6182
rpp6182
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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. :-)
rpp6182
rpp6182
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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.
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20150 Visits: 17244
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" ;-)
rpp6182
rpp6182
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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. :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search