none
Transaction replication : Distribution clean up: distribution failed RRS feed

  • Question

  • Hi I am replicating transaction replication  2014 to 2016 . I am having error with "Distribution clean up: distribution" agent job as follows.

    Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 92
    Could not remove directory '\\networkshareTES\unc\PUB\20191002105458\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory.
    Replication-@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only check rowcou: agent distribution@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only  scheduled for retry. Could not clean up the distribution transaction tables.

    ++++++++++++++++++++++++++++++++++++++

    Work around I did as follows

    1. 

    EXEC master.dbo.sp_configure 'show advanced options', 1
    RECONFIGURE WITH OVERRIDE
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1
    RECONFIGURE WITH OVERRIDE

    2. Jobs running with "distributor_admin" . But I tried it manual cleaning with my access "elevated user" and still failed

    use distribution
    go
    EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72. 

    any one can advise on this please 

    Tuesday, October 29, 2019 3:49 AM

Answers

  • Hi ashwan,

    The login  of distribution agent job has the full control permission to this folder.

    Best regard,
    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

    • Marked as answer by ashwan Friday, November 8, 2019 8:06 PM
    Wednesday, October 30, 2019 10:08 AM

All replies

  • Have you checked to see if there are any processes using that share? For example someone could have a command window open in that directory which underlies the share.

    Sometimes it is simpler to connect to the server hosting the snapshot share navigate to it and try to delete it. This can reveal the problem.

    You also need to verify that the share and directory structure has full control rights to be able to delete the contents.

    Tuesday, October 29, 2019 10:37 AM
    Moderator
  • Hi Hilary great its start working when grated "everyone" on the folder. Now identified as permission issue.

    But I need to limit to the permission as "everyone". I already grated full access to "SQLServer" serviceusers, "Agent" sevice users, replication users for full access. But didnt work. Do you know what  user  we missed and need to include full access ?

    thanks 

    Tuesday, October 29, 2019 1:22 PM
  • Make sure your distribution agent login account has a full access to that folder.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, October 29, 2019 3:21 PM
  • Hi ashwan,

    The login  of distribution agent job has the full control permission to this folder.

    Best regard,
    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

    • Marked as answer by ashwan Friday, November 8, 2019 8:06 PM
    Wednesday, October 30, 2019 10:08 AM