MSRepl_command table growing - cleanup job failing

  • Deb Anderson

    SSCrazy

    Points: 2116

    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

    😎

  • Chandra Sekhara Vyas Dhara

    SSChampion

    Points: 10207

    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.

  • noeld

    SSC Guru

    Points: 96590

    \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

  • Deb Anderson

    SSCrazy

    Points: 2116

    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!

    😎

  • Deb Anderson

    SSCrazy

    Points: 2116

    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.

    😎

  • matthew.harris 22409

    SSC Enthusiast

    Points: 118

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

  • knk.dhyani

    SSC Veteran

    Points: 200

    Worked for me too !! thanks!!

  • mct1960

    SSC Journeyman

    Points: 95

    ...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