Merge replication causing problems

• rjdegraff

  Grasshopper

  Points: 21

  More actions

  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.

• Alejandro Jose Miguel

  Grasshopper

  Points: 14

  More actions

  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