Removing Replication...? 2

[jawan]

Hi, i have replication on my main live DB. Now i want to modify one table and its not allowing me, the error is

- Unable to modify table.

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.Subinfo' because it is being used for replication

How i solve this problem, i do not need replication any more so how can i remove replication without detach or offiline DB.

Any idea please...!

 

[JayKusch]

Give this a shot ...

To avoid this situation, you should always remove replication in the following order:

- Delete the subscriptions
- Delete the publications
- Disable publishing

Deleting publications without unsubscribing or disabling publishing without deleting publications results in this situation.

Anyways, if you are seeing the above mentioned errors, try any or all of the below workarounds one after another in the order described below.

1. EXEC sp_removedbreplication 'your_database_name'

2. Execute the following procedure on the problematic database and pass the table name as the parameter:

EXEC sp_msunmarkreplinfo 'your_table_name'

3. You may not need this method, but take this as a last resort.

EXEC sp_configure 'allow',1
go
reconfigure with override
go
use your_database_name
go
update sysobjects set replinfo = 0 where name = 'your_table_name'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
go


Thanks

J. Kusch

 

[jawan]

Thanks J. Kusch for reply.

Right now DB is extremly busy, i will try it tonight.

Just to make sure, there is no need to offline or detach DB for this procedure...?

Also as you mentioned in numer 3., is there any chances for complications like, any diturbance in table relations or any thing else.

 

[JayKusch]

Backup up your DBs!!! The DBs do not need to be taken offline nor deattached. I have removed replication on several server with no ill effects BUT ... there is always a chance for things to go south if you know what I mean!

Thanks

J. Kusch

 

[jawan]

Thanks,

This DB is 24/7, always, every minute is in use, night time just some users use this DB, is it ok even some users are working during i try to remove replication.

 

[JayKusch]

Should be.

Thanks

J. Kusch

 

[jawan]

In number 3. you mentioned different sp's and commands

if i am not wrong..! these are for just to make modification in a table...? The table right now i can not make any changes due to replication.... Right...? Can you please little explain.

Thanks

 

[jawan]

Thanks J.Kusch
The following command worked. It took more than 1-hour but it removed replication

EXEC sp_removedbreplication 'your_database_name'

One more question, under "Replication Monitor + Publisher" it still showing databse. Do i need to remove this too or is it ok...?

Thanks for your kind help.

 

[cocopud]

Just read your posts and they were very helpful. The replication on our test server was totally messed up. Someone was looking at a restore problem and deleted some of the replication components. After trying numerous things, when the 'exec sp_removedbreplication' command was run, I was then able to use the disable publication wizard and everything appears to be ok. I was about to reinstall everything. Thanks.

Additionally, I was getting alot of error 208s regarding the missing syspublications

 

[stable]

Hi there,

I'm very very new to SQL Server!

There are several redundant tables that are currently published. Hence the message cannot drop the table.

If I delete the subscription (there is only one) via SQL Enterprise Manager, I should be able to modify the publication by deleting those redundant tables and set up the subscription again - is that correct?

Do I set up the subscription via right clicking the publication properties and using the subscription tab and push new?

I don't need to run the stored procedure sp_removedbreplication 'your_database_name' do I?

And if I do where do I run this from?

Thank in advance for any help!
Stable

 

[stable]

Hi there,

Continuing on from my last post:

I've just notice that there are no subscribers currently set up.

when I go into publication name's properties' articles tab and try to remove the check next to the redundant table to be deleted, I get:

Error 21338 Cannot drop article "tablename" from publication "publication name" because its snapshot has been run and this publication could have active subscriptions.

And yet when I look in the subscriptions tab there are no subscribers listed. I guess the error mesage above does say "could have active subscriptions".

Looks like I need to delete the publication. Can I right click the name of the publication in Enterprise manager and select delete from the menu?

How do I disable publishing?

Problem is that I don't know where to type and run the statement:

EXEC sp_removedbreplication 'your_database_name'

Any advice would be greatly appreciated.

cheers
Stable