Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Exclamation Unanswered: Transactional Replication and Log Files

    SQL 2005 (SP1, +2153)
    Windows 2003 (SP1)

    This is not so much a question as a warning/comment.

    I was struggling to understand why the log file on my Merge Replication database was continuing to grow despite having a transaction log backup scheduled hourly.

    I finally ran a DBCC OPENTRAN to see if there were open transactions and I got the following (to me, unusual) result:

    Code:
    Oldest distributed LSN     : (10:384:4)
    Oldest non-distributed LSN : (0:0:0)
    When I googled for answers, everything kept coming back with pointers to SQL Transactional replication.

    We were doing Merge Replication, so I didn't think the various links were relevant. But as I kept coming up empty for a solution to the issue, I finally went and had a look at the publisher properties and scanned down the list of replicated databases (there's just the one). Voila! The database in question was configured for both Transactional and Merge replication.

    How it came to be configured for both Transactional and Merge replication, I do not know. I have a feeling it is somehow embedded in the publication creation script that I created some time ago. When I went to double check, I found the same option set on our Development and Test servers.

    So, if you have a merge replication database and you find that you have a log file that is growing uncontrollably, check the publisher properties and look to see whether your database is enabled for transactional replication.

    Regards,

    hmscott
    Have you hugged your backup today?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Perhaps one of the mods should pop this in the FAQ. Or maybe create an IAQ for it

    Nice gotcha hmscott - I hope someone will benefit from this when they go a-googling
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    Perhaps one of the mods should pop this in the FAQ. Or maybe create an IAQ for it

    Nice gotcha hmscott - I hope someone will benefit from this when they go a-googling
    In a breach of etiquette, I posted a question on this same topic on the MS SQL Replication forum. Hilary Cotter responded with a comment that still has me scratching my head:

    Quote Originally Posted by Hilary Cotter
    At some levels transactional and snapshot replication are considered to be
    the same, for example in sp_replicationdboptions if you want to enable a
    database for snapshot or transactional replication, the parameter you pass
    it the same. They also use the same activex control.
    Not that I doubt Mr. Cotter in the least. But WHY?

    There are so many things about replication that just simply don't jive with the way I would have thought they were. In some cases, it's just a matter of terminology; but in other cases there's a real gap between perceived functionality and actual functionality.

    We approximately 25% of the way through deploying 67 nodes in a dynamic, horizontally partitioned replication solution. I've learned much over the last 18 months... but I am still a grasshopper.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •