none
How to get the date of the oldest record in MSRepl_Commands table in distribution database? RRS feed

  • Question

  • Hello all,

    I feel that the "Agent history clean up: distribution" job is not really working! although the job history shows all successful completions of the jobs runs every 10 minutes, but the distribution database data file just keeps growing! 

    The "Agent history clean up: distribution" job is configured to:

    EXEC dbo.sp_MShistory_cleanup @history_retention = 48

    So I want to make sure that the oldest record in the MSRepl_Commands table is less than 48 hours old.

    Note that my SQL Server version is:

    Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)   Mar 19 2015 12:32:14   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 

    Monday, May 22, 2017 4:57 AM

Answers

  • Hi,

    The "Agent history Clean up" is not deleting from msrepl_commands. It is deleting history only in msdistribution_history and msrepl_errors.

    The Job that is cleaning up msrepl_commnads is "Distribution Clean-up: Distribution db". You need to check that job. If Cleanup job completing and still db is growing u may look into this:

    https://bartoszlewandowski.blog/2017/05/12/when-distribution-cleanup-not-behaving/

    I had similar problem with large volumes coming into distributiondb and cleanup could not handle the load.

    About your question. join msrepl_commands with msrepl_transactions like this:

    select min(entry_time) from MSrepl_commands (NOLOCK) c
    join MSrepl_transactions (NOLOCK) t
    on c.publisher_database_id=t.publisher_database_id
    and c.xact_seqno = t.xact_seqno

    or just select min(entry_time) from msrepl_transactions.

    • Marked as answer by Butmah Tuesday, May 23, 2017 12:28 PM
    Monday, May 22, 2017 1:42 PM

All replies

  • Hi Butmah,


    Yes, when you configure the @history_retention = 48, the cleanup job will delete any historical data older the 48 hours. This method can work in most environment. In your scenario, you can also try to use EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 48.

     

    Best Regards,

    Teige

     


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; 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 <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Monday, May 22, 2017 8:29 AM
    Moderator
  •  

    Thanks Teige, but my question is how to find the date of the oldest row in MSRepl_Commands table?  The job might be working fine, but I want to make sure it is.

    For "EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 48." yes I've used it one time when my distribution database reached to around 200GB, but at the time that was because some authentication error in  "Agent history clean up: distribution" job, which caused it to fall for a couple of weeks without me noticing that (me the lazy DBA didn't set up an alert for that job)....  that was fixed though.

    Again I want to make sure the "Agent history clean up: distribution" job is working as expected.



    • Edited by Butmah Monday, May 22, 2017 9:52 AM
    Monday, May 22, 2017 9:48 AM
  • Hi,

    The "Agent history Clean up" is not deleting from msrepl_commands. It is deleting history only in msdistribution_history and msrepl_errors.

    The Job that is cleaning up msrepl_commnads is "Distribution Clean-up: Distribution db". You need to check that job. If Cleanup job completing and still db is growing u may look into this:

    https://bartoszlewandowski.blog/2017/05/12/when-distribution-cleanup-not-behaving/

    I had similar problem with large volumes coming into distributiondb and cleanup could not handle the load.

    About your question. join msrepl_commands with msrepl_transactions like this:

    select min(entry_time) from MSrepl_commands (NOLOCK) c
    join MSrepl_transactions (NOLOCK) t
    on c.publisher_database_id=t.publisher_database_id
    and c.xact_seqno = t.xact_seqno

    or just select min(entry_time) from msrepl_transactions.

    • Marked as answer by Butmah Tuesday, May 23, 2017 12:28 PM
    Monday, May 22, 2017 1:42 PM
  • Watch out for this. You may find defunct snapshots listed here and if you are using immediate sync the values will be off.
    Monday, May 22, 2017 2:37 PM
    Moderator