Post reply

Truncate Table in a Replicated Environment

  • September 27, 2010 at 9:16 am

    #229759

    In transaction replication, I would like purge a big table from both publisher & subscriber and would like know if I need to do anthing other than issuing a TRUNCATE tblname statement on the publisher database?

  • September 27, 2010 at 11:53 am

    #1227881

    From BOL:

    Restrictions for TRUNCATE:

    You cannot use TRUNCATE TABLE on tables that:

    Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

    Participate in an indexed view.

    Are published by using transactional replication or merge replication

    If your table is published then you cannot truncate it on the publisher.

    One option (depending on your replication setup) is to remove the article from the publication, truncate it and add it back to the publication.

    How big is your big table that you want to truncate (in terms of size and # of rows). Depending on the size of the table you might also be able to create a stored procedure that does a tuned delete and replicate the stored procedure execution so that you execute the SP on the publisher and have it execute on the subscriber instead of transferring all the delete commands.

  • September 27, 2010 at 12:07 pm

    #1227894

    Thanks Winash, I was thinking along the same line:

    remove the article

    truncate table

    add the article

    The table is huge, 50+ gigbyte with a million+ rows, so we aren't going to try a delete procedure.

    Luckily the table doesn't have any FK associated with it.

    Thanks again.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply