Published Database Transaction Log continues to Grow!

A customer recently noticed their Published database transaction log would continue to grow until they backed up the Distribution database.

DBCC OPENTRAN()

Oldest distributed LSN     : (0:0:0)
Oldest non-distributed LSN : (982:100898:1) --> 0x000003D6 : 00018A22 : 001

-- Commit Tran(sp_replshowcmds)  0x000003D6:00018A22:0004
select  [Current LSN],[Operation],[Transaction ID], Left([Description],20)
from::fn_dblog('0x000003D6:00018A22:001','0x000003D6:00018A22:0004')

“… to clear out the Transaction Log file was to backup the Distribution Database System – this appeared to work and the stuck transaction was shifted onwards, as the transaction log file had grown upto 4GB in size. “

 Turns out they had enabled a very specialized Replication feature called “sync with backup”.  This prevents the transaction Log from being truncated until after the Distribution database is backed up.  This feature allow you to restore the Publisher and Distributor and keep transaction flow in sync. A neat feature, but requires careful planning as the Publisher transaction log will grow if the Distributor backups are stopped.  You can see more about this feature in the SQL Books Online.

http://msdn.microsoft.com/en-us/library/ms147311.aspx

To disable coordinated backups: At either the Publisher on the publication database or at the Distributor on the distribution database, execute sp_replicationdboption (Transact-SQL). Specify a value of sync with backup for @optname and false for @value.