Distribution clean up: distribution failed

Executed as user: xxx. Could not remove directory ‘\\ServerName\D$\MSSQL\ReplData\unc\ServerName_Databasename\20130327201941\’. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015) 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. [SQLSTATE 01000] (Message 14152). The step failed.



I first started first with check of xp_cmdshell (it often happens when xp_cmdshell is disabled, somtimes by default on SQL Server 2005 and upper). I did that with the following command in SSMS, by checking if I was able to read the content of the incriminated folder:

exec xp_cmdshell 'dir D:\MSSQL\ReplData\unc\ServerName_Databasename\20130327201941\'

if you can not read the content of this folder you can activate xp_cmdshell with the following command:

EXEC master.dbo.sp_configure 'show advanced options', 1
EXEC master.dbo.sp_configure 'xp_cmdshell', 1

Rights on the folders

In a second time i checked the rights on the folder (from Repldata and under) for the user (service account in AD for my case), giving full rights on the folder.

Process explorer

First of all, I tried to rename the 20130327201941 folder: I had an error, explaning the folder was alreday used

  • I finally deployed Process explorer on my server, and search for 20130327201941 (Find / Find handle or DLL).
  • It returned 2 processes using this folder.
  • Then I closed them
  • I renamed the folder 20130327201941 in 20130327201941_old
  • I ran the Distribution clean up job that ended with success
  • I renamed 20130327201941_old folder in 20130327201941
  • I ran the Distribution clean up job that ended with success

Problem solved..