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.
After running this I was able to successfully change the column definitions for the msrepl_tran_version column and make it nullable.