you're reading...
SQL Server

Replication Settings Not Fully Removed From A Database or How to Clean Up Replication Bits

I had set up replication on a database that was later removed from replication. The database was still active just the need to replicate was gone. I dropped the publication and subscriptions and thought all was fine. As it turns out that does not complete the job. The replication bits are not fully removed from the database. i.e the tables still have the  ‘msrepl_tran_version’ with associated constraints and are still marked as ‘REPLICATED’

The issue arose from a process the development team uses to clone tables when a new product line is opened. They typically generate the table creation scripts off of existing tables and in many cases replicating the msrepl_tran_version column. Not necessarily an issue as the associated constrain provides a default value on insert if none provided. unfortunately down stream there is an automated  process that archives data and apparently attempts to insert a null value into the msrepl_tran_version column. My first thought was to simply remove the column from all the tables, but being on the conservative side and unsure of what other process down stream may have a reference to this column I decided to alter the column instead and make it nullable.

Of course the alter table alter column statement fails with Cannot alter column ‘msrepl_tran_version’ because it is ‘REPLICATED’. “Replicated” I thought? No I removed the subscription, publication and all articles these tables are not participating in replication!. After doing a little research it turns out that simply dropping the subscription, publication and all articles does not remove all the replication information (bits) from the database. Which can also show up after a restore. Enter the “sp_removedbreplication “ stored procedure which removes any residual replication settings.

sp_removedbreplication ‘DB_NAME’

After running this I was able to successfully change the column definitions for the msrepl_tran_version column and make it nullable.

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.


No comments yet.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: