I used the SQL Server 2005 publication wizard to set up transactional replication. It scheduled a job called "Distribution clean up: distribution" that calls sp_MSdistribution_cleanup. The job fails every time it runs. The xp_cmdshell is enabled and SQL Server Agent runs under an account that has sufficient privileges to execute it. The account also has full rights to the repldata subdirectories. I have applied service pack 1.
I looked through the sp_MSdistribution_cleanup code and it looks like there are two stored procedures and one function missing from the SQL server databases. They are
- master.dbo.sp_MSunc_to_drive
- sys.fn_escapecmdshellsymbolsremovequotes
- sys.sp_MSdrop_distribution_agentid
Where can I get a copy of these stored procedures The expired distribution data is slowly consuming my remaining hard drive space. Thanks.
Joe
Replication distribution cleanup error
AhmetBolac
I'm really sorry, but the clean up agent already deleted the history. I think part of the message was "Could not clean up the distribution transaction tables."
Joe
Erkki
Glad to hear that the problem is solved. The issue mentioned in my post is a minor issue and should not cause any functional problems. It would be helpful for other visitors of this thread if you can post the error message you're seeing before it's fixed (it should still be available in replication agent history).
Thanks,
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
Nate Skousen
Hi Kamol,
We have exactly the same scenario. We looked for possible solutions from other forums and advices, but no one can solve this.
But one weird stuff we did tried...
1) rename the folder (say your example folder name 20071115181062_temp)
2) run the job
(this will fail of course)
3) rename back the folder to it's original folder name (20071115181062)
4) run the job
olah! for us it worked.. try it....
but of course, we need solid solution for this.... any help/advice is greatly appreciated!
WCF_Thomas80
Hi, Joe,
There seems to be a bug in our code where we should not call master.dbo.sp_MSunc_to_drive, instead we should call sys.sp_MSunc_to_drive. In SQL Server 2005, we have move most of the system sps to mssqlsystemresource database, and some of them are hidden from end users as they're not supposed to be used directly by customers. sys.sp_MSunc_to_drive, sys.fn_escapecmdshellsymbolsremovequotes and sys.sp_MSdrop_distribution_agentid are hidden sps/function thus you don't see them but they do exist.
I will do more research and see if there is a workaround.
By the way, can you post the exact error message you're seeing from distribution cleanup job (by using replication monitor to view the job history) Are you using dynamic snapshot merge replication Any specific information about your replication setup would be helpful.
Thanks,
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
Jonathan Sealby
It is snapshot replication with PULL subscription. Occurs once a day.
DISTRIBUTOR and SUBSCRIBER are on the same server.
Snapshot folder: \\myserver1\replication\ which is physically on DISTRIBUTOR.
MS SQL 2005 v.9.00.3042.00
Here is job's error log (actual names are changed):
Date 11/15/2007 5:57:26 PM
Log Job History (Distribution clean up: distribution)
Step ID 1
Server MYSERVER1
Job Name Distribution clean up: distribution
Step Name Run agent.
Duration 00:00:01
Sql Severity 16
Sql Message ID 20015
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: mydomain\#mylogin$. Could not remove directory '\\myserver1\replication\unc\MYSERVER2_db1_db2\20071115181062\''. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
When manually running EXEC distribution.dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 0 getting following:
Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
Could not remove directory '\\myserver1\replication\unc\MYSERVER2_db1_db2\20071115181062\'. 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.
xp_cmdshell is enabled:
exec master.dbo.sp_configure 'xp_cmdshell'
xp_cmdshell 0 1 1 1
Login mydomain\#mylogin$ has full_controll access to \\myserver1\replication\unc\ folder and it's subfolders and files. I even added user EVERYONE with full_control permissions for test purposes - still getting the same error.
When manually running sp_MSreplremoveuncdir SP getting "ACCESS DENIED" error. Job is running under sysadmin account. Distributor is behind FIREWALL and there is firewall rule between them on port 1433.
It seems there is error due MS SQL security on DISTRIBUTOR cause when i am running MS DOS command through command prompt on PUBLISHER to remove that DIR on DISTRIBUTOR it is working fine. But when i am trying to use xp_cmdshell within MS SQL Server on DISTRIBUTOR with network path it is failing, but again it is working fine with local path. Does xp_cmdshell needs specific firewall rule for any other port beside 1433
If it is an account security issues how come the same account can create SNAPSHOTS on DISTRIBUTOR and at the same time CAN'T delete them
Thank you.
Eric Kehr
Thanks for answering me so quickly. The problem has been resolved. The account under which xp_cmdshell was executing did not have the proper access to the repldata share.
Joe