SQLServerWiki

“The Only Thing That Is Constant Is Change”

How to manually clean up SQL Server replication.

Posted by database-wiki on April 11, 2016

Problem Description:

The best option is to use the sp_removeDBreplication. However, if the remove SP
fails, additional clean up may be needed.

The following script can be used to manually clean up replication. I would
recommend first removing the objects which gave errors in the remove replication
SP, then rerunning. sp_removeDBreplication. For example, if the Conflict table
cleanup fails, delete the conflict tables using the script then rerun. The cleanup
SP is sill the most thorough.

Resolution

— Modify the Select and Drop statement as needed.
— Copy the output to another Query Analyzer window.
— Verify no user defined objects are in the list before executing the DROP.

SET NOCOUNT ON
Declare @objname varchar(100), @message varchar(128)
Declare ImageCursor Cursor FOR
Select name from sysobjects where name like ‘%conflict%’ and type=’u’

OPEN ImageCursor
FETCH NEXT FROM ImageCursor into @objname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = ‘Drop table ‘ + @objname
PRINT @message
— execute (@message) — recommend don’t execute directly, but copy output to new
windows to run
FETCH NEXT FROM imageCursor into @objname
END

CLOSE imagecursor
DEALLOCATE imagecursor

—————————–

Select name from sysobjects where name like ‘%conflict%’ and type=’u’
SELECT @message = ‘Drop table ‘ + @objname

Select name from sysobjects where type = ‘tr’ and status < 0 and category = 2
SELECT @message = ‘Drop Trigger ‘ + @name

Select name from sysobjects where name like ‘sp_%’ and type =’p’ and category = 2

SELECT @message = ‘Drop Procedure ‘ + @name

Select name from sysobjects where name like ‘tsvw%’ and type =’v’ and category = 2

SELECT @message = ‘Drop View ‘ + @name

Select name from sysobjects where name like ‘ctsv%’ and type =’v’ and category = 2

SELECT @message = ‘Drop View ‘ + @name

Select name from sysobjects where name like ‘MSMerge%’ and type=’u’
SELECT @message = ‘Drop table ‘ + @objname

———–IF there are objects created with a different logon (i.e. owner is not
dbo), the drop statement can be modified to include the user name.
prefix———-

Select name from sysobjects where type = ‘tr’ and status < 0 and category = 2
SELECT @message = ‘Drop Trigger <username>. ‘ + @name

Reference:

Problem Description:
You are trying to remove merge replication from your SQL Server 2000 SP3 instance
and sp_removedbreplication fails with a “does not exist” error when trying to drop
one of the conflict tables.

Criteria for Resolution:
Remove merge replication from the database ServiceDesk5 on your development system
without the above error.

Steps Taken:
* Manually removed replication related objects:
Select ‘drop table ‘ + name from sysobjects where name like ‘%conflict%’ and
type=’u’
Select ‘drop trigger ‘ + name from sysobjects where type = ‘tr’ and status < 0 and
category = 2
Select ‘drop proc ‘ + name from sysobjects where name like ‘sp_%’ and type =’p’ and
category = 2
Select ‘drop view ‘ + name from sysobjects where name like ‘tsvw%’ and type =’v’
and category = 2
Select ‘drop view ‘ + name from sysobjects where name like ‘ctsv%’ and type =’v’
and category = 2
Select ‘drop table ‘ + name from sysobjects where name like ‘MSMerge%’ and type=’u’

* Followed the steps in the below article so that your user objects were no longer
marked as being used in replication.
326352 PRB: You Receive Error 3724 When You Drop a Non-Replicated Object
* Verified with “select * from syscolumns WHERE colstat & 4096 <>0” that none of
the columns were marked for replication. 811899 PRB: Error 4928 Occurs When You Try
to Rename a Non-Replicated Column

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: