Fix : Msg 4928, Level 16, State 1, Line 9 Cannot alter column 'XXXXXX' because it is 'REPLICATED'

 Msg 4928, Level 16, State 1, Line 9

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

I got this error when I tried to increase table column data type length from 150 to 300. I have removed the article from publication but no luck still error coming.

 

Finally the below procedure helped me to fix the issue :

This article had been previously removed from Two publications. So the above error should not have occurred...   We have a consistency issue. 

 Diagnostic  1: Check syscolumns.colstat.

 

Select * from syscolumns where id = object_id(<table name>)

The value of the colstat field for the particular column was 8192. I've run into this before on SQL 2k. The fix at that time was to update this field to 0 for the offending column. If you attempt that now, you get the following error :

Msg 259, Level 16, State 1, Line 2

Ad hoc updates to system catalogs are not allowed.

Either way... the 8192 is going to be an issue. I don't have the luxury of putting my production server into single user mode to modify system catalogs... so my fix needs to be real time and online.

Diagnostic 2: check sys.columns

Select is_non_sql_subscribed ,* from sys.columns where object_id = object_id(<table name>)

If the is_non_sql_subscribed field = 1, you have an issue. This can happen on servers with any type of replication including SQL Server only.  I'm currently running SQL 2k5 replication in my environment. No third party replication. Either way , the value returned for me was "1" which identifies an inconsistency issue. 

When the snapshot agent creates a new snapshot for your publication it sets this field to 1, once the snapshot is finished the agent resets the value back to 0. Our solution will use this snapshot agent behavior.

 

Workaround:

Create a new publication with your questionable article. Snapshot it. Check the data. SQL will attempt to fix your inconsistencies and properly flag the is_non_sql_subscribed field. Drop the article from your new publication, make your table edits and add the article back to your original publications.