THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Damian Widera

How to rename a column when CDC is enabled

I have been recently asked if Always On supports metadata operations on tables. The operation is add, drop and column rename. I said that it was possible. However I was informed that during column rename there was an error about “replication”:

Caution: Changing any part of an object name could break scripts and stored procedures.Msg 4928, Level 16, State 1, Procedure sp_rename, Line 655. Cannot alter column 'Name' because it is 'REPLICATED'.

I know that replication is not used in the scenario so my next guess was that this must be caused by the CDC. And I was right. Let’s do a simple example to see how the CDC works and what happened if you try to rename a column.

I downloaded the AdventureWorks2014 database from the codeplex page and enabled the CDC in this database. I created also a role that could be used to have access to the CDC data. However it is not important in our example.

 USE AdventureWorks2014;


CREATE ROLE [cdc_admin]


EXEC sys.sp_cdc_enable_db



Next I configured that all CDC-related objects are stored in the separate filegroup – that is for performance reason:

ALTER DATABASE AdventureWorks2014



ALTER DATABASE AdventureWorks2014

ADD FILE (    NAME = N'CDC_Objects',

                     FILENAME = N'C:\Temp\Adwentureworks_cdc.ndf',

                     SIZE = 1024MB , FILEGROWTH = 1024MB )



The last configuration step is to choose the table that will be under the control of the CDC feature.  You might indicate the role, capture instance name (which is important in case where there are two CDC tables) and filegroup name at the moment.

EXEC sys.sp_cdc_enable_table

       @source_schema = N'Sales',

       @source_name = N'Store',

       @role_name = N'cdc_admin',

       @capture_instance = N'SalesStore',

       @filegroup_name = N'CDC_Objects';


As the CDC configuration is done I was trying to rename on of the column that belongs to the table Sales.Store:

EXEC sys.sp_rename 'Sales.Store.Name' , 'SomeNewBetterName', 'COLUMN'


I was immediately notified by the error:

Caution: Changing any part of an object name could break scripts and stored procedures.

Msg 4928, Level 16, State 1, Procedure sp_rename, Line 655

Cannot alter column 'Name' because it is 'REPLICATED'.


I checked the sys.columns view and that was true – the flag is_replicated was set to all columns that are configured for the CDC.

It makes perfect sense for me as the CDC feature uses log reader agent J to detect changes.

Change data capture and transactional replication always use the procedure sp_replcmds to read changes from the transaction log.


SELECT is_replicated FROM sys.columns

WHERE object_id = object_id(N'Sales.Store')

AND name = N'Name'


What should we do in case of column rename? There is only one way I am aware of. First the CDC must be disabled for the table. But be careful before you run the statement because all related CDC tables will be dropped immediately and without any warning J

EXEC sys.sp_cdc_disable_table

       @source_schema = N'Sales',

       @source_name = N'Store',

       @capture_instance = N'all';



Finally it is possible to rename the column.

EXEC sys.sp_rename 'Sales.Store.Name' , 'SomeNewBetterName', 'COLUMN'


If all changes are done the CDC should be enabled again

EXEC sys.sp_cdc_enable_table

       @source_schema = N'Sales',

       @source_name = N'Store',

       @role_name = N'cdc_admin',

       @capture_instance = N'SalesStore',

       @filegroup_name = N'CDC_Objects';



And that's all for today! 




Published Thursday, April 30, 2015 9:16 PM by Damian
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Anonymous said:

But now you've lost all of the history! That may not be not ideal.

What you need to do, in case you do want to retain history, is to capture the min_lsn column from the cdc.change_tables table, for the table you're modifying. Then put all of the existing rows into a temp table or something and THEN disable CDC on the table. Re-enable it, then re-insert all of the rows from the temp table into the new CDC change table. Finally, update the min_lsn to the one you captured prior to starting the process.

With a bit of work and creativity you can put all of this logic into a DDL trigger. There are various edge cases and weird areas but it's not too difficult to come up with a good solution.


April 30, 2015 3:52 PM

Damian said:

Adam, thanks for the comment. You are right!

That is why I wrote " be careful before you run the statement because all related CDC tables will be dropped immediately and without any warning" :)

I should have written that approach in the article, too but I just wanted to express that a simple column rename is not that simple when the CDC is enabled on a table.

See you in Wrocław on the SQL Day conference in two weeks!

May 1, 2015 1:30 AM

Cody said:

Adam what's the source and significance of min LSN? Is there a better description of this somewhere?

June 10, 2015 3:02 AM

Damian said:


I will try to do post number two with Adam solution in it. We spoke that briefly but I have had not time since last month to finish the post!

June 10, 2015 4:46 AM

Brad said:

I came across this post while searching for a paticular issue that I'm trying to deal with related to SQL Server CDC.  I have a CDC-enabled table that needs to have a column added.  We do the alter, create a 2nd capture instance, copy the original capture instance into the new one, drop CDC, re-enable CDC, and copy the data from the 2nd capture instance back into the newly established one (that will now have the new column).  Last thing we do is grab the min(__$StartLSN) from the capture instance and update the cdc.change_tables with this value.  This is what then makes CDC crap out.

what am i missing?

thanks in advance!


June 14, 2016 1:46 PM

zzyytt said:">">">">

May 1, 2018 7:01 PM

chenyingying said:


May 6, 2018 11:12 PM

linying123 said:


May 10, 2018 8:35 PM

shenyuhang said:


June 1, 2018 7:11 PM

dongdong8 said:



June 29, 2018 3:25 AM

linying123 said:



July 16, 2018 8:55 PM

dongdong8 said:



July 23, 2018 11:40 PM

yaoxuemei said:


August 15, 2018 2:13 AM

chenjinyan said:


August 22, 2018 11:12 PM

shenyuhang said:


August 23, 2018 10:26 PM

xiaozhengzheng said:

2018-11-20 xiaozhengzheng

November 19, 2018 8:30 PM

yanmaneee said:">">

June 29, 2020 11:10 PM

Leave a Comment

Privacy Statement