Log Shipping

Question

Post reply

Log Shipping

Nunnery

Ten Centuries

Points: 1293
More actions
September 28, 2016 at 1:59 pm

#317865

After performing a fail-over to my secondary database I'm a bit unsure what step(s) I should take next as the former-primary server is no longer online, but the SQL Jobs are still attempting to run on the fail-over server. Would I want to execute the sp_delete_log_shipping_secondary_primary procedure?
Goal: The secondary database is now the only instance running and will not be participating in log shipping moving forwarded.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

That stored procedure does exactly what you need to do.

-- Gianluca Sartori

Nunnery Ten Centuries Points: 1293 More actions · Answer 2

When I attempted to run the stored procedure I received the following error message:

exec sp_delete_log_shipping_secondary_primary @primary_server = 'OldPrimaryServer', @primary_database = 'OldPrimaryDatabase'

Primary Server OldPrimaryServer Database OldPrimaryDatabase has active log shipping secondary database(s) on the secondary. Drop the secondary database(s) first.

Would it cause any issues I were to just manually remove the LSCopy, LSRestore, LSAlert Server Agent jobs along with the two alerts for log shipping in the Alerts folder?

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

Have you tried just right-clicking on the database, selecting Transaction Log Shipping and removing the tick from "Enable this as a primary database in a log shipping configuration"?

Nunnery Ten Centuries Points: 1293 More actions · Answer 4

This originally was the secondary database in the log shipping configuration so when I go into the properties this particular setting is unchecked.

djj SSCoach Points: 18831 More actions · Answer 5

Do not know how to but, what about manually disabling the log shipping stored procedures (LSCopy... and LSRestore...)?

Nunnery Ten Centuries Points: 1293 More actions · Answer 6

djj (9/29/2016)
Do not know how to but, what about manually disabling the log shipping stored procedures (LSCopy... and LSRestore...)?

Since I received the earlier error when I ran sp_delete_log_shipping_secondary_primary, I've disabled the LSAlert, LSCopy, and LSRestore jobs. I doubt there would be any issue with just deleting these, but I imagine there are a few references for the log shipping inside one of the system databases since you're able to view status information in the Transaction Log Shipping Status report.

rvsc48 SSCertifiable Points: 7652 More actions · Answer 7

Or, have not done this in a while, in ssms, go the properties of the database on the Primary server, then go to Transaction Log Shipping, and uncheck the "Enable this as a primary database in a log shipping configuration" - this should completely remove all log shipping from the database. It will also, supposed to, remove the backup, copy, and restore jobs from both the primary and secondary automatically.

Nunnery Ten Centuries Points: 1293 More actions · Answer 8

RVSC48 (9/29/2016)
Or, have not done this in a while, in ssms, go the properties of the database on the Primary server, then go to Transaction Log Shipping, and uncheck the "Enable this as a primary database in a log shipping configuration" - this should completely remove all log shipping from the database. It will also, supposed to, remove the backup, copy, and restore jobs from both the primary and secondary automatically.

Unfortunately, the primary (former) server is no longer available and has been decommissioned.