Post reply

Snapshot Replication and T-log not truncating

  • February 14, 2019 at 7:11 am

    #409513

    Hi
    I have snapshot replication enabled on a database.
    T-log backups every half hour. Snapshot occurs once daily and changes are copied successfully to the subscriber.
    The log_resue_wait_desc from sys.databases shows REPLICATION.
    All the vlf's from dbcc loginfo show a status of 2. 
    Using DBCC OPEN TRAN and the  function fn_dblog I have worked out the transaction ID that may be responsible. 
    SELECT * from fn_dblog(null, null) WHERE [Transaction ID]= '0000:000574af

    This returns 39 rows with the 1st row showing a value of ALTER TABLE in the column Transaction Name.
    I have the option to replicate schema changes enabled.
    I have read that when schema changes are made then this transaction is not cleared down when replication is enabled, and the vlf will stay at a status of '2', thus preventing any other vlfs 'behind' this vlf in the log chain from clearing down and to also remain at '2'???

    Can somebody confirm if this is the case and how to go about fixing if any future schema changes are made to the replicated articles?

  • February 14, 2019 at 8:08 am

    #2022074

    PearlJammer1 - Thursday, February 14, 2019 7:11 AM

    Hi
    I have snapshot replication enabled on a database.
    T-log backups every half hour. Snapshot occurs once daily and changes are copied successfully to the subscriber.
    The log_resue_wait_desc from sys.databases shows REPLICATION.
    All the vlf's from dbcc loginfo show a status of 2. 
    Using DBCC OPEN TRAN and the  function fn_dblog I have worked out the transaction ID that may be responsible. 
    SELECT * from fn_dblog(null, null) WHERE [Transaction ID]= '0000:000574af

    This returns 39 rows with the 1st row showing a value of ALTER TABLE in the column Transaction Name.
    I have the option to replicate schema changes enabled.
    I have read that when schema changes are made then this transaction is not cleared down when replication is enabled, and the vlf will stay at a status of '2', thus preventing any other vlfs 'behind' this vlf in the log chain from clearing down and to also remain at '2'???

    Can somebody confirm if this is the case and how to go about fixing if any future schema changes are made to the replicated articles?

    Yes that is what happens. It can happen when the option to replication schema changes is enabled. You want that disabled since changes are propagated with the each snapshot. 
    To fix the log and clean things up, mark the transaction as distirubted by executing: 
    EXEC sp_repldone null, null, 0,0,1
    Then set the replicate_ddl to 0 for the publication
    EXEC sp_changepublication
        @publication = 'PublicationName',
        @property = N'replicate_ddl',
        @value = 0
    Then run the snapshot agent.

    Sue

  • February 14, 2019 at 8:20 am

    #2022078

    Thanks Sue.

    So, confusingly, you have to set the option for schema changes to be disabled - then they get copied over in the fresh snapshot? Ok I will make that change.

    Thanks for clearing this up.

  • February 14, 2019 at 11:52 am

    #2022099

    PearlJammer1 - Thursday, February 14, 2019 8:19 AM

    Thanks Sue.

    So, confusingly, you have to set the option for schema changes to be disabled - then they get copied over in the fresh snapshot? Ok I will make that change.

    Thanks for clearing this up.

    You don't really need it with snapshot replication. Schema changes are automatically propagated with each synchronization.

    Sue

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply