Distribution Agent fails with “Could not remove directory” error
Sateesh Yele
Microsoft SQL Server Support
Symptom:
The Distribution Clean up job fails with the following error.
Executed as user: Domainname\Username. Could not remove directory ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData\unc’. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
Consider the following scenario.
SQL Server is running under account DomainName\UserA and the SQL Agent is running under DomainName\UserB. Distribution cleanup job is running under the SQL Agent account. The UserB account has full permissions on repldata folder and xp_cmdshell is enabled on the server.
When the distribution clean up job runs, it fails with the above error.
If the Distribution Clean up job step is manually executed from the query analyzer, the following error message is reported.
Query for distribution cleanup job step:
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72
Error:
Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
Could not remove directory ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData\unc ‘. 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.
Resolution:
Distribution cleanup job fails with the above error because the SQL Server account does not have full control permissions on ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData’ folder.
Give full control permissions to repldata folder for SQLServerMSSQLUser$<Servername>$MSSQLSERVER group.
Right click on the repldata folder and go to properties. Select the security tab. Under group or user names section select SQLServerMSSQLUser$<Servername>$MSSQLSERVER and under permissions section check full control and modify and click OK.
Be sure to also check the "Sharing" permissions on your Snapshot folder, if applicable. In the above example, if the error message references a unc path, e.g. "\ReplServerReplData…", then this may be a clue to also verify Sharing permissions.
It worked. Thanks
I tried this, and I received "Access Denied", I am admin on machine.
I tried this and worked for me. Thanks a lot!
Even an admin can deny themselves permissions. Plus, access denied trumps all other permissions. However, an admin has the ability to remove the denial (unless the domain admin imposed it:).
Worked just fine for me, except in my case the only SQL Server account I could add was SQLServer2005SQLBrowserUser
Thank you, this solved my problem!
Thanks a lot!