Snapshot Replication and T-log not truncating

Question

Snapshot Replication and T-log not truncating

PearlJammer1

SSCrazy Eights

Points: 8480
More actions
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?

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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

PearlJammer1 SSCrazy Eights Points: 8480 More actions · Answer 2

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.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

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