how to remove distribution db from publisher?

hpradhan08
hpradhan08 used Ask the Experts™
on
Hi there,
How I remove distribution db from publisher? I'm in the process of moving the distribution db. I removed the subscribers, publishers and old distribution db. Now, I'm trying to re-stablish the Transactional Replication. I see the old distribution db info is on the publisher's protiperties... How I do remove it from the publisher and re-configured with the new distribution db from new location? It's very urgent. Please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Aaron ShiloChief Database Architect

Commented:
Check the sp_dropdistributor @no_checks = 1

Before executing please check the details of this sp here:
http://technet.microsoft.com/en-us/library/ms173516.aspx 

Author

Commented:
I ran that earlier using that ms provided doc and got the following errors:
Msg 14166, Level 11, State 1, Procedure sp_replicationdboption, Line 203
Disable publishing ignored error msg 3906, severity 16, state 2: Failed to update database "Advantage" because the database is read-only..
Msg 3930, Level 16, State 1, Procedure sp_replicationdboption, Line 240
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 266, Level 16, State 2, Procedure sp_replicationdboption, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_dropdistributor, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
Aaron ShiloChief Database Architect

Commented:
why is database " Advantage " in read only mode. ?

it looks like you need to change it for readonly to readwrite in order to update system tables.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

1. You need to script out all your publications first with "To create or enable components". You can do that from the MS right click on the publication and then generate script. Save them to file for later use.

2. Secondly you need to use the "Disable Publishing and Distribution" on that server and you do that from MS again by right clicking on the disable on the Replication node node then on that option. A wizard will open and when you click next choose the first option: "Yes, Disable distribution...".This will drop all replication information on the publisher and its subscribers and on the distributor.

3. After this step you configure the new distributor by right click on Replication node and "Configure Distributor". Follow the wizard steps there and add the new distributor.

4. After that you will recreate the publications using the script at step1. Run the snapshots and everything should be fine.
When you create the distributor make sure you give a network location for the snapshots where they will be created when needed.

Author

Commented:
That's the kicker... The Advantage db is in read/write mode. The server, where distribution lives that's our log shipped server. On that server we've log shipped Advantage db which is standy mode. I didn't get it why it's grabbing that db?
Aaron ShiloChief Database Architect

Commented:
>> I didn't get it why it's grabbing that db?

because every action on the database is log shipped to the other server .
Had to manually remove. Thank you.

Author

Commented:
Please close this. Thank you.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial