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:
When I googled for answers, everything kept coming back with pointers to SQL Transactional replication.
Oldest distributed LSN : (10:384:4)
Oldest non-distributed LSN : (0:0:0)
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.
Have you hugged your backup today?