May 3, 2017 at 5:35 am
My Distribution DB is keep on growing and the Distribution clean job fails with the below error.
I have added the login XYZ in the Administrators group of windows and provided sysadmin permissions to it even then the job fails with the same message.
Executed as user:XYZ. You do not have sufficient permission to run this command. Contact your system administrator. [SQLSTATE 42000] (Error 14260) 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.
May 3, 2017 at 11:46 pm
Hi,
did the cleanjob fail if you try to execute as a user, or did the job fail, if you try to schedule the job?
May 4, 2017 at 4:05 am
it fails in both ways.
May 4, 2017 at 10:39 am
You could try running the command from the job your self - it usually is:
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72
executed in the distribution database. Double check the job though - job should be Distribution Clean up: Distribution.
Also check if xp_cmdshell is enabled. And check the snapshot folder for the permissions. The user XYZ needs full control
Sue
May 8, 2017 at 7:27 am
Enabled xp_cmdshell and XYZ has full permissions on snapshot folder.
still the job fails with same message.
Executed as user: XYZ. You do not have sufficient permission to run this command. Contact your system administrator. [SQLSTATE 42000] (Error 14260) 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.
May 8, 2017 at 7:43 am
And what happened when you ran it manually? Also - who owns the job?
Sue
May 8, 2017 at 8:03 am
I ran the command as part of the job Distribution Clean up: Distribution,owner of the job 'sa'
May 8, 2017 at 8:13 am
DBA_007 - Monday, May 8, 2017 8:03 AMI ran the command as part of the job Distribution Clean up: Distribution,owner of the job 'sa'
And what happened when you ran the command manually?
If you execute the following in a query window while in the distribution database:
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72
Does it complete? Do you get any errors?
Sue
June 15, 2017 at 8:19 pm
Can you set up profiler while you run the stored procedure manually and also catch the errors / exceptions in the profiler ?
Please try to check then at what statement it is erroring out
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply