Avatar of hpradhan08
hpradhan08Flag for United States of America

asked on 

how to remove distribution db from publisher?

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.
Microsoft SQL Server

Avatar of undefined
Last Comment
hpradhan08
Avatar of Aaron Shilo
Aaron Shilo
Flag of Israel image

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 
Avatar of hpradhan08
hpradhan08
Flag of United States of America image

ASKER

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.
Avatar of Aaron Shilo
Aaron Shilo
Flag of Israel image

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.
SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Zberteoc
Zberteoc
Flag of Canada image

When you create the distributor make sure you give a network location for the snapshots where they will be created when needed.
Avatar of hpradhan08
hpradhan08
Flag of United States of America image

ASKER

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?
Avatar of Aaron Shilo
Aaron Shilo
Flag of Israel image

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

because every action on the database is log shipped to the other server .
ASKER CERTIFIED SOLUTION
Avatar of hpradhan08
hpradhan08
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of hpradhan08
hpradhan08
Flag of United States of America image

ASKER

Please close this. Thank you.
Avatar of hpradhan08
hpradhan08
Flag of United States of America image

ASKER

Thanks
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo