0

I neede some help with this problem.

Using MS SQL Server, I have a replication setup and working from Server A to server B. There is a clean up Job that has to run on the agent -

'Distribution clean up: distribution'

and it is failing with the error

Message
Executed as user: domain\servername$. Could not remove directory '\\servername\repldata\unc\servername$PublicationName\20240626113002\'. 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.

On the server, in that directory I have permission set correctly (I think) for the service user 'domain\servername'. enter image description here

The Job step "Run as" option is blank, and is not giving me ability to select a user. enter image description here

And If I attempt to run that stored procedure run manually, EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72, I get the same error.

I realized that that 'domain\servername$' is not the user under which the SQL Server is running, not the user under which an agent is running. and hence, I do not know how the job (or rather a step) is set to run under it. So, I would not mind chaning it to something, I feel like I have more control over.

I am somewhat lost at this point. I feel something is incomplete, but I don't know what

Dmitriy Ryabin
  • 111
  • 3

1 Answers1

0

From the error you are seeing they are related to permission and process access issues. From your screenshots, The user has all the necessary permissions to access the file path \\servername\repldata\unc. You could confirm whether the xp_cmdshell is enabled and if it’s disabled, you need to reenable.

The other thing you need to check is the row count error message. Replication-@rowcount_only parameter must be the value 0,1, or 2. 0=7. This parameter only allows these values 0, 1, or 2. Ensure that in your script this value is within the allowed values.

Severalnines
  • 419
  • 1
  • 5