Transaction Log won’t truncate when using Snapshot Replication to Azure SQLDB
Microsoft SQL Server Escalation Services
Customer encountered transaction log growth for a Published SQL Server 2016 database while nightly refreshing Azure SQLDB. We discovered the default publication setting “replicate_dll” was still enabled.
<your published database>.[dbo].[syspublications]
description name replicate_ddl
—————————————————— ———- ————-
Snapshot publication of database ‘AdventureWorks2014’ Customers 1
(1 row(s) affected)
When enabled, table schema changes are tracked in the Transaction Log and not cleared unless either 1) publication is dropped, or 2) sp_repldone is manually executed.
1) Drop\Create Snapshot Publication
To change the publication settings, first script out your Snapshot publication then change to @replicate_dll = false and run the script to recreate the Snapshot Publication
sp_addpublication . . . @replicate_ddl = 0
2) sp_repldone null, null, 0,0,1
If running sp_repldone to clear REPLICATE from transaction log, first change the Publication properties, Subscription options.
Next verify in published database syspublications table you’re only publishing SNAPSHOT publications as sp_repldone will mark all transactions as REPLICATED including those pending for Transactional Pulications. If you have a mixture of Transaction and Snapshot publications, use option 1) Drop Snapshot Publication.