Post reply

Merge replication causing problems

  • November 9, 2004 at 3:13 pm

    #84098

    We are running two databases in parallel. One is on our old servers

    (clustered) and the other is on our new servers (separate domain with XP

    2003 Server and active directory). The old servers are running SQL 7 and the

    new are running SQL 2000. Here are the basics of what happens on both

    systems

     
    -receive two text files every hour containing around 7000 records each

    -the files are massaged, then bulk inserted into two tables

     
    Each month we create a new database for that month. On the old server, the

    databases grow to around 600 meg. The new system uses merge replication to

    keep two copies of the database in sync. Before I enabled replication

    everything worked fine. A bulk insert took 1-4 seconds to complete. Once I

    enabled replication (backup server uses a pull subscription) I noticed

    something strange.

     
    -insert times started increasing slowly and now take up to 8 minutes to

    complete

    -databases are growing obscenely large (1.7 gig after around two weeks)

     
    Once I turn off replication and do a dbdd shrinkdatabase the sizes and

    insert times return to normal. The table sizes should only increase in size

    around 16 bytes per record (for the row guid) but actually increase

    five-fold. Something is horribly wrong but I don't know what. Can anyone

    suggest anything?

     
    further info
     
    DBNAME: EMS_YYYY_MM (where YYYY_MM is the current month and year)

    TABLE1: Analog00 (analog data from the top of the hour)

    TABLE2: Analog05 (analog data from 5 minutes after the hour)

     
    fields

        Point_Index (int)

        Date_Stamp (datetime)

        Time_Stamp (tinyint)

        Point_Value (real)

        Point_Quality (tinyint)

        DST_Offset (tinyint)

       

    total record size (data) is 19 bytes plus 16 bytes for rowguid (when merge rep is enabled)

     
     


    A man who chases two rabbits will catch neither.

  • November 13, 2004 at 8:13 am

    #530026

    Merge replication keeps track of changes to published tables (articles) through metadata tables (triggers are created on each article and help to keep track of the changes you do). As you keep doing changes those tables will continue to increase.

    The tables I mention are MSmerge_contents, MSmerge_tombstone to mention just the most important ones.

    Merge replication can flush the metadata rows that have been aready replicated or that are beyong the experitation date regulated with merge retention cleanup. What regulates how often it runs, you should change the default retention period down from the default of 14 days.

    There is much more than that. I suggest you look into books online for further details.

    Alejandro Miguel

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

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