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