Question about "Distribution clean up: distribution" Job

Can the manner in which you remove a publication effect the ability of this
job to run?
I had been experiencing errors with this job. The error message was as
follows:
"Executed as user: <SQLServerAgentDomainAccount>. Could not remove directory
'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGD AILYADT\20061221142453\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed."
I run my SQL Server Agent process with an account that does not have
sysadmin rights. After reading a few other posts with this same error, I
experimented with running the job as a user with sysadmin rights.
Unfortunately, I still had the same error.
Then I went into the job step and copied the command it was attempting to
run. This is the command:
"EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72"
I pasted this command into a query window and attempted to run it under my
own login, which is a member of the sysadmin role and is also a member of the
Local Administrators group. It still could not run.
The folders the command was trying to delete were used for Publications that
no longer existed. So I went ahead and deleted the folders manually. Now
the command ran successfully.
I am thinking something went wrong during the process of removing the
publications and subscriptions. Anyone have any idea what might have
happened?
Is this SQL 2005? If so you need to enable xp_cmdshell.
If it is SQL 2000 you need to ensure that no one else is accessing this
directory.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:6E882295-B0F5-4FA8-A3ED-731CFB672615@.microsoft.com...
> Can the manner in which you remove a publication effect the ability of
> this
> job to run?
> I had been experiencing errors with this job. The error message was as
> follows:
> "Executed as user: <SQLServerAgentDomainAccount>. Could not remove
> directory
> 'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGD AILYADT\20061221142453\'.
> Check the security context of xp_cmdshell and close other processes that
> may
> be accessing the directory. [SQLSTATE 42000] (Error 20015). The step
> failed."
> I run my SQL Server Agent process with an account that does not have
> sysadmin rights. After reading a few other posts with this same error, I
> experimented with running the job as a user with sysadmin rights.
> Unfortunately, I still had the same error.
> Then I went into the job step and copied the command it was attempting to
> run. This is the command:
> "EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
> @.max_distretention = 72"
> I pasted this command into a query window and attempted to run it under my
> own login, which is a member of the sysadmin role and is also a member of
> the
> Local Administrators group. It still could not run.
> The folders the command was trying to delete were used for Publications
> that
> no longer existed. So I went ahead and deleted the folders manually. Now
> the command ran successfully.
> I am thinking something went wrong during the process of removing the
> publications and subscriptions. Anyone have any idea what might have
> happened?
|||It is SQL Server 2005 and xp_cmdshell was already enabled.
"Hilary Cotter" wrote:

> Is this SQL 2005? If so you need to enable xp_cmdshell.
> If it is SQL 2000 you need to ensure that no one else is accessing this
> directory.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:6E882295-B0F5-4FA8-A3ED-731CFB672615@.microsoft.com...
>
>
|||Hi Ken,
I am guessing that the SQLServerAgent service does not have sufficient
rights to remove folders under P:\ReplData. As such, you may want to try
right-clicking the folder in Windows Explorer and grant the SQLServerAgent
service account Full Control rights on it.
-Raymond
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@.microsoft.com...[vbcol=seagreen]
> It is SQL Server 2005 and xp_cmdshell was already enabled.
> "Hilary Cotter" wrote:
|||You're right. Originally, the Agent service account had not been granted
access to the Snapshot folder (P:\ReplData). However, I was still receiving
the error even after I granted the service account "Full Control" over the
share.
"Raymond Mak [MSFT]" wrote:

> Hi Ken,
> I am guessing that the SQLServerAgent service does not have sufficient
> rights to remove folders under P:\ReplData. As such, you may want to try
> right-clicking the folder in Windows Explorer and grant the SQLServerAgent
> service account Full Control rights on it.
> -Raymond
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@.microsoft.com...
>
>
|||I am guessing (again) that the 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.
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@.microsoft.com...[vbcol=seagreen]
> You're right. Originally, the Agent service account had not been granted
> access to the Snapshot folder (P:\ReplData). However, I was still
> receiving
> the error even after I granted the service account "Full Control" over the
> share.
> "Raymond Mak [MSFT]" wrote:
|||You were right. I granted the SQL Server Service Account "Full Control" over
the Snapshot Folder and the job has been running successfully ever since.
Thanks for your help.
"Raymond Mak [MSFT]" wrote:

> I am guessing (again) that the 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.
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@.microsoft.com...
>
>