SQL log_reuse_wait_desc Replication Version 2016

Question

Hi,

We have an SQL server that replicates one database. Replication type is Snapshot. This basically snapshot the database twice a day.

The transaction logs keep increase in Size, which we have found is caused by Replication. If we run log_reuse_wait_desc we get a result of Replication. So Checkpoint cannot be created on the DB.

I have read through a few fixes online none look suitable or are work arounds rather than a fix, also most look to be fixes for SQL 2012.

There is a fix where you stop the Schema from replicating which could be the correct fix. Yet when I run sp_helppublication 'DBName' I keep getting syntax error near sp_helppublication

What I would like to know how to I set the Replicate_DDL in SQL 2016 to 0.

Is there anything else that might be stopping causing the log_reuse_wait_desc = Replication.

If it possible that there was an old transaction replication that has not be cleanly delete causing an issue. (I cannot see any indication of this in the GUI.)

Any other suggestions.

Thanks for you time in Advance

Craig

Answer 1

Hi @Craig Garland ,
I tested on my own. It works fine to me:

Answer 2

Hi Panda,

I will review doing this on the weekend.

What were the OPENTRAN result before running this command?

Thanks

Answer 3

Hi

So I managed to fix the problem over the weekend.

In the end we needed to run the following command
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @TIME = 0, @EGCC = 1
See link
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-repldone-transact-sql?view=sql-server-ver16

So if Log_reuse_wait_desc is set to replication
DBCC OpenTrans is showing old transaction
and you don't have any transaction replication running you can run this command and it should fix the issue.

Also, it is suggested to change Replicate Schema Changes to False (Under subscription options). This should stop the problem reoccurring. Have not confirm this yet as it's only been a few days since the fix.

Note setting Replicate Schema Changes to false is the same as running.
EXEC sp_changepublication
@Publication=’PublicationNameHere’,
@Property=N’replicate_ddl’,
@value=0

Hope this helps someone :)

Regards
Craig