SQL SERVER – FIX – Error – Msg 4928, Level 16, State 1. Cannot Alter Column Because it is ‘Enabled for Replication or Change Data Capture’

One of my Facebook followers sent me an interesting situation, where he faced error related replication or change data capture.

Hi Pinal,
We have a database in our QA environment which is restored from Production (we have replication set up in Production). In QA environment, we don’t have any replication or Change Data Capture (CDC). When we are trying to rename one of the fields in the table in the Lab and got the following error:

--Rename column 
EXEC sp_rename 'dbo.Foo.Bar', 'Bar1', 'COLUMN';
GO

Msg 4928, Level 16, State 1, Procedure sp_rename, Line 611
Cannot alter column ‘Bar’ because it is ‘enabled for Replication or Change Data Capture’.

I have verified the following:

• None of the tables have is_tracked_by_cdc = 1.
• None of the tables or columns have is_replicated = 1
• Replication was already disabled by using sp_removedbreplication.

Any ideas?
Thanks in advance.
John S.

This was not a pretty situation because the error message is not in line with the data points which they have already verified. I strongly felt that somewhere they have metadata incorrect.

Based on my search on the internet, I found that below has helped few of the others.

execute sp_replicationdboption 'DBName','Publish','False',1

But it didn’t work. So, I thought of playing with CDC by enabling and disabling again because this was a restored copy of the production database.

WORKAROUND/SOLUTION

I have provided below script to enable and disable CDC one more time on the table.

USE YOUR_DATABASE_NAME
GO
EXEC sys.sp_cdc_enable_db
GO
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo'
	,@source_name = N'foo'
	,@role_name = NULL
	,@filegroup_name = N'primary'
	,@supports_net_changes = 1
GO
EXEC sys.sp_cdc_disable_table @source_schema = N'dbo'
	,@source_name = N'foo'
	,@capture_instance = N'ALL'
GO
EXEC sys.sp_cdc_disable_db
GO

As soon as the above was done, we could rename the column.

Have you encountered any such weird situations where the error message was not correct due to metadata problem?

Reference: Pinal Dave (https://blog.sqlauthority.com)