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.