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

Share this Post

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]

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

Share this Post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.