Transaction Log won’t truncate when using Snapshot Replication to Azure SQLDB

Chris Skorlinski
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.

SELECT [description],[name],[replicate_ddl]

FROM
<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.

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx

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

exec
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.