MSRepl_command table growing - cleanup job failing

  • I've tried to get an answer out of TechNet on this ... they are very quiet.

    Here is my problem:

    My distribution database is growing because the distribution cleanup job is failing.

    I have a separate distribution server - SQL 2005.

    My Distribution cleanup job is failing with the following error:

    Executed as user: \REPLDATA_BK\unc\LNGOKCSQLP003_BANKRUPTCY_BK-SMALLER TABLES A-C\20071203122870\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.

    However the error in the agent log says this:

    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.

    When I log in as the agent service account (also the account the job is running under) and exec the following:

    exec xp_cmdshell 'dir d:\'

    I don't have any problem.

    Other people have seen this, but so far I've seen no resolution. I'm hoping you guys have some wisdom.

    thanks,

    Deb

    😎

  • SQL Server Agent service account is not what xp_cmdshell running under in the cleanup scenario, another suspect will be

    the SQL Server service account. In any case, you should be able to find out

    exactly which security account is doing the delete using the filemon.exe

    tool from sysinternals.com.

  • \REPLDATA_BK\unc\LNGOKCSQLP003_BANKRUPTCY_BK-SMALLER TABLES A-C\20071203122870is a SNAPSHOT folder. What is failing on that error is the Snapshot Agent.

    You should check for the credentials of that snapshot Job.

    Secondly the Cleanup Job will log the actual error on the Job Log, If it does not you should start by restartting it and updating the stats on the MSRepl_commands table..


    * Noel

  • I did run filemon - good tool btw!

    It is my service account user that is getting the AccessDenied on that directory.

    However, I've given that account Full Control.

    I moved all my replication distribution to a brand new server, too. So, this has now occurred in two separate environments.

    aaaaaaack!

    😎

  • ha! I am happy to report that I have found issue and resolved this problem.

    My SHARE security was different than my NTFS security on my snapshot folder.

    I was able to see this easily when I went into Computer Management/Shared Folders/Shares. I added my account that my distribution agent was running under and gave it full control.

    PROBLEM SOLVED!

    Thanks for everyone's feedback.

    😎

  • You are a legend. I know you wrote this 6 years ago - but your Post just solved my exact same problem!

  • Worked for me too !! thanks!!

  • ...and for me too. 🙂

    Check the job step log entry for the "Executed as user:", right at the start of the "Message".  This is what I gave the access to and fixed my issue.

Viewing 8 posts - 1 through 8 (of 8 total)

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