Using SQL Server 2000 with SP3, I have a database that uses merge replication. The database file is only 42 megs, but the transaction log file has grown to over 2 gigs.

I currently have 34 subscribers, all of which use dynamic filters for specific tables; however, the number of tables using a dynamic filter is very small. In addition, the retention period for the subscriptions is unlimited, because the subscribers are sales field reps who are intermittently connected. Unfortunately, I thought that 30 days would be long enough and was proven wrong in the past.

I am concerned about the size of my transaction log, as it is taking longer and longer for the backups to occur, and eventually I know I will run out of space.

I have tried backing up the database with the option to truncate the log, and the file sizes are not reduced. If I manually attempt to shrink the database files, it only decreases the transaction log by about 200 megs, but continues to grow and can't be reduced any further.

I have run dbcc opentran and it reported:

Oldest active transaction:
SPID (server process ID) : 57
UID (user ID) : 1
Name : ClearChangeBits
LSN : (5236:2552:1)
Start time : Oct 28 2003 4:16:16:857PM

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (4928:69:1)

I am concerned about the oldest distributed LSN and the oldest non-distributed LSN. This seems like a lot of active transactions.

I have thought about using sp_repldone; however, I read that it was only for transactional replication. I'm not sure this would work on a merge publication.

I am looking for ways to reduce the size of the transaction log. If possible, I need to do so without taking the database down or having to re-create the subscriptions on each subscribers system.

Any suggestions would be greatly appreciated.