Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

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

Discussion in 'Official Microsoft News' started by Chris Skorlinski [MSFT], Mar 29, 2017.

Thread Status:
Not open for further replies.
  1. Chris Skorlinski [MSFT]

    Chris Skorlinski [MSFT] Guest

    Blog Posts:
    0
    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/sqlserverfa...-or-shrinked-due-to-snapshot-replication.aspx

    1) DropCreate 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.

    [​IMG]

    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.

    Continue reading...
     
Thread Status:
Not open for further replies.

Share This Page

LiveZilla Live Chat Software