Distribution Cleanup failed due to security reasons


Recently we got following error in Distribution Cleanup for Replication 


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.



the job was failing and we are getting this error in the job history


Could not remove directory 'E:\MSSQL\ReplData\unc\xx_xxx_xx\20121010114212\'. 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).  

AS this message suggests its clearly related to security reson. 
The account which is used in SQL Server agent doesn't have access to the folder for  E:\MSSQL\ReplData.

In such a case you can give full access on E:\MSSQL\ReplData  to the account which is used in SQL Server Agent  
Or
You can give access  to the users role on the folder, if you are not sure which account is used in SQL Server Agent account. 

 

No comments:

Post a Comment

Popular Posts