October 15, 2012 at 9:43 am
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?
October 15, 2012 at 9:50 am
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:
[font="Courier New"]
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.[/font]
The job failed with this message in the SQL Agent log:
[font="Courier New"]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.
[/font]
Thank you in advance for your help. 🙂
October 15, 2012 at 11:37 am
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.:
[font="Courier New"]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
[/font]
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.
[font="Courier New"]USE msdb
GO
delete FROM log_shipping_monitor_secondary
WHERE secondary_server = 'vmaxBirmSQL1'
GO
[/font]
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.
October 15, 2012 at 11:38 am
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" 😉
October 15, 2012 at 11:58 am
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. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply