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 ,

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

Pls check this link : https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-schema-changes?view=sql-server-ver16

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

I think your deman is that you are not sure if there is an old transaction replication makes you in the situation that "log_reuse_wait_desc = Replication" and log size keep increasing?
If so you can try the BIG MOVE：

sp_removedbreplication 'XXXX'  

wish you good luck !

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 2

Hi PandaPan,

Thanks for your response. Following the link you provided I was able to change the Replicate schema changes from True to false. This has not fix the issue but I am waiting for the next replication to confirm.

What I meant by an "old transaction replication" is that I cannot see any other replication using the SQL Manager GUI but is it possible that there are hidden replication jobs that can be found using SQL script or other method. If all replication jobs are shown in the GUI then I don't see any transaction replication jobs so this is not the issue.

Thanks for your time.

Regards
Craig

Answer 3

Hi @Craig Garland ,
I saw your replications are snapshot, you can try the following statement:

use distribution--in distributor server  
  
if not exists(select 1 from sys.tables where name ='MSreplservers')  
  
begin  
  
select job.name JobName, a.name AgentName , publisher_db,publication, s.data_source as publisher,  
  
case publication_type  
  
when 0 then 'Transactional'  
  
when 1 then 'snapshot'  
  
when 2 then 'Merge'  
  
end as publication_type  
  
   From MSsnapshot_agents a inner join sys.servers s on a.publisher_id=s.server_id  
  
   inner join msdb..sysjobs job on a.job_id=job.job_id  
  
   
  
end  
  
else  
  
begin  
  
select job.name JobName, a.name AgentName, publisher_db,publication, s.srvname as publisher,  
  
case publication_type  
  
when 0 then 'Transactional'  
  
when 1 then 'snapshot'  
  
when 2 then 'Merge'  
  
end as publication_type  
  
   From MSsnapshot_agents a inner join MSreplservers s on a.publisher_id=s.srvid  
  
   inner join msdb..sysjobs job on a.job_id=job.job_id  
  
end  

I got the answer from this official link: https://techcommunity.microsoft.com/t5/sql-server-support-blog/how-to-find-sql-server-replication-related-jobs-and-t-sql/ba-p/1530496

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 4

Hi Panda,

So I tried the script but keep getting an error with the first line.
Use Distribution
Msg 911, Level 16, State 1, Line 1
Database 'distribution' does not exist. Make sure that the name is entered correctly.

I did use Replication Monitor and checked for any replication in this. It only showed the ones I know about. At this stage I am going to assume there are not Replication not shown.

I have set the Replicate_DDL to 0 using the GUI. This has not fixed my issue, but I will try a restart of the SQL this weekend and see if it kicks in then.

Regards
Craig

Answer 5

Hi,

I found this site which looks to have a similar issue to ours.
https://technoagile.wordpress.com/2017/12/29/log_reuse_wait_desc-showing-as-replication-and-also-snapshot-publication-configured/

They suggest running the following commands to set the Oldest Non-distributed LSN value to 0. What I would like to know is if this is safe to run or could it create a problem?

EXEC sp_changepublication
@Publication=’PublicationNameHere’,
@Property=N’replicate_ddl’,
@value=0

Thanks for your time in Advance.

Regards
Craig G