SQL Server Performance

Cannot modify a column if it's replicated

Discussion in 'Performance Tuning for SQL Server Replication' started by ferol, Mar 3, 2004.

  1. ferol New Member

    I have replication setup between two SQL 2000 servers, I want to change the
    size of a column on the publisher and I get the following error. How can I
    modify a column if it's replicated?
    Cannot drop the table 'dbo.test' because it is being used for replication.

    Thanks in advance..
  2. satya Moderator

    You will have to drop the subscription. Execute sp_droparticle with appropriate parameters to drop the table from publication and then drop the table. You can then recreate subscription.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. ferol New Member

    I'm a newbie with replication .Sorry for my lack of knowledge. Do you have any instructions on how to do this? Can I do this from Enterprise managager using the replication folder?

    Thank you
  4. satya Moderator

    Review information listed in books online for SP_DROPARTICLE topic.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. ChrisFretwell New Member

    I'd suggest more reading on replication in general too.
    After you drop the article and make the change, you'll have to start the replication again. This will trigger a snapshot for that table. If the table is huge, there will be a load on the publisher for a short bit (and you need enough drive space for the table snapshot), but the real fun can be at the subscriber. A snapshot will drop the subscriber table, recreated, bring in the data and then apply all the indexes. If this is a small table, no big deal, but if its a large table your subscriber database applications may have to wait until this is finished.
    Its not complicated, but worth reading about in BOL before you start changing things.
  6. ferol New Member

    Here is what I did;<br />Right -click and go to properties on the publication<br />Choose filter columns tab<br />I unchecked the column I wanted to exlude from replication<br />Then I went to subscriptions tab and then hit reinitialize<br />Then I went back to the snapshot agent and start the agent.<br />As soon as I did that it start recreating and replicating the whole database. I just wanted one column to be dropped but instead it replicate the whole database, all the tables and columns which took forever.<br /><br />What do you think I can do? they told me that modifying is easier to do in SQL2000 <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />Thank you for your help.
  7. satya Moderator

    When any schema changes occurs SQL itself will take care and replication process reinitialises.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. TRACEYSQL New Member

    Do you know how to remove just one table...im in the filter columns.
    i see all the checks on the columns......but i can't see how to remove it on the key.
    Doing this does this release the table
    as i cannot drop the table...
  9. satya Moderator

    Check your other post and do not duplicate the problem.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

Share This Page