none
replication RRS feed

  • Question

  • Hi All,

    I see log file for a DB keep growing. Log_reuse_wait_desc shows as 'replication'.

    What can be done to fix the issue?

    I see many forums stating the below command would help clearing the tlog:

    EXEC sp_repldone @xactid = NULL,
      @xact_segno = NULL, @numtrans = 0,
      @time= 0, @reset = 1

    But is it okay to run this cmd as it would clear all the pending transactions & mark it as all done?

    • Moved by Tom Phillips Monday, October 7, 2019 12:10 PM replicaiton question
    Monday, October 7, 2019 9:53 AM

All replies

  • https://blogs.msdn.microsoft.com/sqlserverfaq/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication/

     After that, backup the transaction log and see if it still grows.

    Make sure that SCHEMA changes are replicated 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 7, 2019 10:42 AM
  • Hi Uri Dimant,

    Thanks for the reply. But in my case, transactional replication has been configured & the changes are replicated using tlogs.

    I am afraid using 'repldone' would cause data inconsistency.

    Monday, October 7, 2019 11:06 AM
  • You often see the advice to run sp_repldone based on the assumption that you aren't using replication and there is something left from earlier that need to be cleaned up.

    This doesn't seem to be the case, so just treat replication as an open transaction (since that is how it behaved according to the log truncation). Use the regular mechanism, checking your VLF structure (DBCC LOGINFO, state 2) etc. IF you want something replication specific (i.e., troubleshoot the replication process, then I suggest you check out the replication forum (since you are more likely to find replication experts there).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, October 7, 2019 12:01 PM
  • sp_repldone basically says that you have read all commands to be replicated from the log and they have been written to the distribution database. As you can see, this might cause replicated data not to make it to the subscriber.

    There are probably 2 conditions you are running into.

    1) there is a large batch operation being read out of the log. Check the log reader agent messages to see if it is scanning, or consistently writing commands and transactions to the distirbution database. If it says scanning, it is likely reading data from the log from a large transaction, or there is a not of logged data in your log for the log reader agent to read through - for example an indexing operation.

    2) You might have an application lock from a snapshot/log reader agent holding a transaction open

    Dbcc OpenTran can help to see this.

    Monday, October 7, 2019 1:18 PM
    Moderator
  • Hi SSG92,

    >> I am afraid using 'repldone' would cause data inconsistency.

    Yes, it may cause data inconsistency. I suggest you waiting for the transaction synchronized and then shrink the log file.

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, October 8, 2019 9:41 AM