Solved

Deleting Log Shipping

Posted on 2011-03-17
4
201 Views
Last Modified: 2012-08-14
Hi.  ServerA (2005) was log-shipping to ServerB to have a warm failover.

However, ServerB has completely bitten the dust and is with us no longer.  ServerA, however, is not easily letting go of this log-shipping relationship.

Going into the GUI DB Properties of ServerA allows me to turn it off--theoretically--but the subsequent dialog after disabling log-shipping never ends.  I think it's still waiting to hear from ServerB, who ain't never coming home.

I think I've killed the LS jobs in Agent.

How do I, as gracefully as possible, end this relationship?  End log-shipping for certain databases on ServerA?

Thanks.
0
Comment
Question by:bamapie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35165917
If it's the only other server for which replication is configured, can you delete the publisher on the main server? If not, can you at least delete the publication? If you do, it will have nothing more to replicate, so that should end the relationship. If it was push replication, you may also be able to delete the subscription from the publisher as well to help end that relationship.

Though this is what the GUI should be doing, you may have more luck doing the TSQL directly instead of using the GUI:

http://msdn.microsoft.com/en-us/library/ms147833.aspx
0
 

Author Comment

by:bamapie
ID: 35180496
Thanks, Ryan.  I just don't know how to set this up, though.

Their example looks like this:

DECLARE @publication AS sysname;
SET @publication = N'AdvWorksProductTran';

-- Remove a transactional publication.
USE [AdventureWorks2008R2]
EXEC sp_droppublication @publication = @publication;

It's just not clear to me, however, what the value that they assign as 'AdvWorksProductTran' would be in my situation.

Thanks
0
 

Accepted Solution

by:
bamapie earned 0 total points
ID: 35181561
Awesome.  Here it is:

sp_delete_log_shipping_primary_secondary
    @primary_database = 'stuck_db',
    @secondary_server = 'log_ship_target_server',
    @secondary_database = 'stuck_db'
0
 

Author Closing Comment

by:bamapie
ID: 35221266
This worked well and did what I needed when I couldn't get anything else to work.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
date diff with Fiscal Calendar 4 77
average of calculation (TSQL) 4 36
T-SQL: How to extract records into a new table 7 21
Please explain Equi-join 3 14
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question