Post reply

MSrepl_commands table is increasing continuously.

  • April 18, 2012 at 5:35 am

    #260156

    Dear All,

    We are using Sql server 2005 stsndard eddition.

    We aer maintaining Transactional replication with update.

    Form 3 days onwards we are observing MSrepl_commands is increasing.And the jobs are running fine.

    It is having 1 croe records.Due to this disribution DB size is increasing.

    Please help me to find out the root cause and solve this issue.

    Thanks in advance.

  • April 18, 2012 at 5:40 am

    #1475263

    Is the subscriber in sync?

    M&M

  • April 19, 2012 at 12:38 am

    #1475850

    Hi,

    yes it is in sync.

  • April 19, 2012 at 12:45 am

    #1475856

    Can you check the immediate_sync property in the publication database

    use publication_db_name

    go

    sp_helppublication

    is immediate_sync =1 ?

    M&M

  • April 19, 2012 at 2:15 am

    #1475885

    The value is Zero.

  • April 19, 2012 at 2:34 am

    #1475891

    ok... Can you provide us this information

    1)

    use distribution

    go

    sp_helpdistributiondb

    What is the value of min_distretention and max_distretention

    2) Is the Distribution clean up: distribution job running? When did it last run?

    3) What is the size of the distribution database now?

    M&M

  • April 19, 2012 at 5:11 am

    #1475939

    What is the value of min_distretention and max_distretention

    >>>min is 0 and max is 72

    2) Is the Distribution clean up: distribution job running? When did it last run?

    >>>Job is running for every 10 mins.10 mins back also it has been run.

    3) What is the size of the distribution database now?

    >>>Size of distribution morning it is 2gb and nw it is 3gb.

    Note:The publication DB size is only 1GB.

  • April 19, 2012 at 5:27 am

    #1475943

    Could you check the row count of MSrepl_commands before and after the distribution clean job runs.

    The data in the MSrepl_commands table in distribution database should be reducing. Also your immediate_sync property is disabled, so the data in the distribution database would be deleted after it is sent to the subscriber.

    Please let us know us the row count of MSrepl_commands before and after the distribution clean job runs.

    M&M

  • April 19, 2012 at 5:31 am

    #1475946

    Hi Mohammed,

    Could you check the row count of MSrepl_commands before and after the distribution clean job runs.

    Before the job please find the row count of MSrepl_commands

    Row count:4658791

    After the job please find the row count of MSrepl_commands

    Row count:4861893

    Data is increasing but it is not decreasing.

    Also your immediate_sync property is disabled,

    How would we know that immediate_sync property is disabled?

    When i executed below query the Value it is showing "Zero".That means is it disabled?If we change the value to "One",that means is it enable.

    --published database

    select immediate_sync, * from syspublications

    Please let me know if you need more information.

    Thank you for your support.

  • April 19, 2012 at 5:39 am

    #1475949

    Please clarify on question

    M&M

  • April 20, 2012 at 1:01 am

    #1476440

    Could you check the row count of MSrepl_commands before and after the distribution clean job runs.

    Before the job please find the row count of MSrepl_commands

    Row count:4658791

    After the job please find the row count of MSrepl_commands

    Row count:4861893

    Data is increasing but it is not decreasing.

    Also your immediate_sync property is disabled,

    How would we know that immediate_sync property is disabled?

    When i executed below query the Value it is showing "Zero".That means is it disabled?If we change the value to "One",that means is it enable.

    --published database

    select immediate_sync, * from syspublications

    Please let me know if you need more information.

    Thnak you for your support.

  • April 20, 2012 at 1:03 am

    #1476441

    Could you check the row count of MSrepl_commands before and after the distribution clean job runs.

    Before the job please find the row count of MSrepl_commands

    Row count:4658791

    After the job please find the row count of MSrepl_commands

    Row count:4861893

    Data is increasing but it is not decreasing.

    Also your immediate_sync property is disabled,

    How we would know that immediate_sync property is disabled.

    When i executed below query the Value it is showing "Zero".That means is it disabled?If we change the value to "One",that means is it enable.

    --published database

    select immediate_sync, * from syspublications

    Please let me know if you need more information.

    Thnak you for your support.

  • April 20, 2012 at 1:04 am

    #1476442

    Could you check the row count of MSrepl_commands before and after the distribution clean job runs.

    Before the job please find the row count of MSrepl_commands

    Row count:4658791

    After the job please find the row count of MSrepl_commands

    Row count:4861893

    Data is increasing but it is not decreasing.

    Also your immediate_sync property is disabled,

    How would we know that immediate_sync property is disabled?

    When i executed below query the Value it is showing "Zero".That means is it disabled?If we change the value to "One",that means is it enable.

    --published database

    select immediate_sync, * from syspublications

    Please let me know if you need more information.

    Thank you for your support.

  • April 20, 2012 at 1:06 am

    #1476443

    Could you check the row count of MSrepl_commands before and after the distribution clean job runs.

    Before the job please find the row count of MSrepl_commands

    Row count:4658791

    After the job please find the row count of MSrepl_commands

    Row count:4861893

    Data is increasing but it is not decreasing.

    Also your immediate_sync property is disabled,

    How would we know that immediate_sync property is disabled?

    When i executed below query the Value it is showing "Zero".That means is it disabled?If we change the value to "One",that means is it enable.

    --published database

    select immediate_sync, * from syspublications

    Please let me know if you need more information.

    Thank you for your support.

  • April 20, 2012 at 8:27 am

    #1476677

    Minto Minto(quendans) (4/20/2012)

    Could you check the row count of MSrepl_commands before and after the distribution clean job runs.

    Before the job please find the row count of MSrepl_commands

    Row count:4658791

    After the job please find the row count of MSrepl_commands

    Row count:4861893

    Data is increasing but it is not decreasing.

    Also your immediate_sync property is disabled,

    How would we know that immediate_sync property is disabled?

    When i executed below query the Value it is showing "Zero".That means is it disabled?If we change the value to "One",that means is it enable.

    --published database

    select immediate_sync, * from syspublications

    Please let me know if you need more information.

    Thank you for your support.

    It looks like your data is getting time to sync with the subscriber from the publisher. In that case, the contents from the msrepl_commands\msrepl_transactions would not be purged even after the distribution cleanup job runs (even if immediate_sync is 0).

    In order to view the number of commands that are yet to be sent to the subscriber, you could run below command in the distribution database.

    select * from distribution.dbo.MSdistribution_status

    M&M

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply