December 6, 2007 at 1:13 pm
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
😎
December 6, 2007 at 10:25 pm
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.
December 10, 2007 at 2:28 pm
\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
January 24, 2008 at 12:58 pm
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!
😎
August 29, 2008 at 9:09 am
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.
😎
December 9, 2014 at 7:39 am
You are a legend. I know you wrote this 6 years ago - but your Post just solved my exact same problem!
July 19, 2016 at 12:46 pm
Worked for me too !! thanks!!
February 3, 2017 at 2:19 am
...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.
July 6, 2022 at 9:49 am
I also have found the issue and resolved my problem, though the root cause was slightly different.
I deleted the distribution database and then recreated it again using the same path. That path still had old snapshots so when the job tried to delete those snapshots, it threw that error.
I had to delete those snapshots manually then I was able to delete any new snapshots using the SQL agent job without getting that error.
September 6, 2022 at 7:01 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply