September 25, 2003 at 12:14 pm
I'm stuck with a nasty replication problem. I need to drop replication on my server, and used the system tools to do so. The process fell over near the end and has left me with one merged article still attached. The article has 1.5m records, and when i try to remove it by any means I recieve an error that the transaction log is full. I have increase the log space to 5Gb, the database itself is only 7Gb, but this has made no difference, it still fills even when i have truncated it in advance. I cannot offer any more capacity and i'm now stuck in a half way position.
How can i remove replication manually? System functions & sp_dropmergearticle just don't work.
Any thoughts welcome
Innes
September 25, 2003 at 1:42 pm
I don't understand...are you trying to remove the table that was published?
Droping replicacion is just deleting the records in all the system tables referencing that publication or configuration, not its articles.
September 25, 2003 at 1:53 pm
I am trying to stop a database being a publication. Somewhere there must be a reference to the fact that the database is still a publcation. And it appears that it is the fact that there is a merge article that cannot be dropped prevent the system deleting the publication.
September 25, 2003 at 2:18 pm
to drop a publication, first delete all the subscription...
run sp_helpmergesubscription from the published db. Then drop those subscriptions with sp_dropmergesubscription.
Once all the subscriptions have been deleted, drop the publication with sp_dropmergepublication.
If there are no more publications in the db, run
sp_replicationdboption @dbname = ?, @optname = N'merge publish', @value = N'false'
and then delete the table. Check in BOL for all the sp parameters you need
September 25, 2003 at 2:26 pm
Done all of that, subscriptions went a long time ago, issued the instruction to drop the publication as per BOL and my own experience.. but it comes back to the same issue. It has to remove all the merge articles as part of the drop publication process, reaches the table i mention, hits the wall with the problem i said... no success at all..
September 25, 2003 at 2:32 pm
Dropping the publication doesn't have to delete any article. can you tell us wich article is that?
September 25, 2003 at 3:14 pm
The article is called MatrixCellDynamic, and I just tried the sp_dropmergepublication again, message back from query anylser after 20 mins was :
Server: Msg 9002, Level 17, State 2, Procedure sp_MSarticlecleanup, Line 140
The log file for database 'HQSample' is full. Back up the transaction log for the database to free up some log space.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 16 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
September 26, 2003 at 7:20 am
sp_MSarticlecleanup will only drop tables created for the management of the replication, likes views, SPs, conflic tables...all used for replication.
This article MatrixCellDynamic is it a user Table?.
September 26, 2003 at 8:57 am
did you try the following
sp_removedbreplication 'database name'
Make sure that all subscription to this publication have been deleted before running the sp.
September 29, 2003 at 12:27 pm
Nothing worked, in the end i deleted all the conflict tables and the replication tables in the database, dettached it, uninstalled SQL server, reinstalled it, and reattached it... not ideal, but the only thing that worked..
I'm sure theres a more perfect solution out there, but documentation concerning where all the information about replication is held and what it means is very thin on the ground. Having said that, i know 10 times more now than i did before!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply