SQL log_reuse_wait_desc Replication Version 2016

Craig Garland 286 Reputation points
2022-11-21T01:58:22.117+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,318 questions
0 comments No comments
{count} votes

8 answers

Sort by: Most helpful
  1. PandaPan-MSFT 1,911 Reputation points
    2022-11-21T06:11:39.663+00:00

    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"

    1 person found this answer helpful.
    0 comments No comments

  2. Craig Garland 286 Reputation points
    2022-11-21T22:13:23.82+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  3. PandaPan-MSFT 1,911 Reputation points
    2022-11-22T02:17:09.373+00:00

    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"

    0 comments No comments

  4. Craig Garland 286 Reputation points
    2022-11-23T23:25:35.717+00:00

    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

    0 comments No comments

  5. Craig Garland 286 Reputation points
    2022-11-28T01:05:23.717+00:00

    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

    0 comments No comments