|
|
You're in the
sql server replication
group:Distibution db cleanup will not run
sql server replication:
Hello
My appologies if this gets posted twice...I tried to post earlier but it didn't seem to work.. I'm a newbie to replication and was wondering if someone could help. I've noticed that a job named 'Distribution clean up: distribution' (i believe this was created automatically when replication was setup) has not been working over the past few days. When I try to run it manually I get the following error message Executed as user: 'ACCOUNT_NAME'. Could not remove directory '\\SERVER_NAME\C$\MSSQL\Repl\unc\REPLICATION_NAME_Tabl3d0a0ad\20040125190012\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015) Associated statement is not prepared [SQLSTATE HY007] (Error 0) 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 checked that unc folder- it exists however it is empty. I do not have any other processes[to my knowledge that is :)] that are trying to access that directory Does anyone know what could be causing this I'd appreciate any help Thanks Joh
I think you will find that you have an open dos box in this directory or
perhaps you or someone else are accessing it using file explorer. [quoted text, click to view] "John" <jpappas@rd.com> wrote in message noticed that a job named 'Distribution clean up: distribution' (i believenews:CC2627B8-688E-4284-A8D3-88E449186C2F@microsoft.com... > Hello, > > My appologies if this gets posted twice...I tried to post earlier but it didn't seem to work... > > I'm a newbie to replication and was wondering if someone could help. I've this was created automatically when replication was setup) has not been working over the past few days. [quoted text, click to view] > '\\SERVER_NAME\C$\MSSQL\Repl\unc\REPLICATION_NAME_Tabl3d0a0ad\20040125190012> When I try to run it manually I get the following error message: > > Executed as user: 'ACCOUNT_NAME'. Could not remove directory \'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015) Associated statement is not prepared [SQLSTATE HY007] (Error 0) 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. [quoted text, click to view] > any other processes[to my knowledge that is :)] that are trying to access> I checked that unc folder- it exists however it is empty. I do not have that directory. [quoted text, click to view] > > Does anyone know what could be causing this? > > I'd appreciate any help. > > Thanks, > > John >
Hi John,
Thank you for using the newsgroup and it is my pleasure to help you with you issue. As my understanding of you problem, you notice that the Distribution Clean Up Agent is not running and you started it manually but encountered error like: 'Executed as user: 'ACCOUNT_NAME'. Could not remove directory '\\SERVER_NAME\C$\MSSQL\Repl\unc\REPLICATION_NAME_Tabl3d0a0ad\20040125190012 \'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015) Associated statement is not prepared [SQLSTATE HY007] (Error 0) 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.' Right? You are right that the Distribution Clean Up Agent created automatically when replication was setup. It is among the the Miscellaneous Agents folder in Replication Monitor, they are to clean up and monitor different replication processes. Every 10 minutes, the Distribution Cleanup Agent removes replicated commands from the distribution database (default). The Agent also removes all snapshots that have already been applied to the Subscriber from the snapshot folder and that are older than 72 hours (default). Running this Agent ensures that there is space for new snapshot files. It will using using sp_msadd_mergereplcommands which calls xp_cmdshell. You could right-click the Distributor Clean Up Agent and choose the 'Agent History' to the history of the activity of it. From the information you provided and the error message, I wonder: 1) You should check if there are any other process that is using the directory, you could use FileMon from: http://www.sysinternals.com/ntw2k/source/filemon.shtml 2) Confirm that this account you start the agent have the permission to run 'xp_cmdshell', when login Query Analyzer with this account, try to run 'exec xp_cmdshell 'dir c:\' 3) Confirm that the the account you start the agent have full control over the directory: \\SERVER_NAME\C$\MSSQL\Repl\unc\REPLICATION_NAME_Tabl3d0a0ad\20040125190012\ Hope this helps and I am looking forward to you resply! Thanks Best regards Baisong Wei Microsoft Online Support ---------------------------------------------------- Get Secure! - www.microsoft.com/security This posting is provided "as is" with no warranties and confers no rights. Please reply to newsgroups only. Thanks.
Hi Baisong,
Thanks for your reply. I tried what you said to do: 1)I used filemon to see if there are any other processes using that directory and there are not 2)I was logged in as the 'sa' account to start the agent....I logged into Query Analyzer as 'sa' and was able to succesfully execute 'exec xp_cmdshell 'dir c:\' 3)I was able to execute the following line as 'sa': 'exec xp_dirtree '\\SERVER_NAME\C$\MSSQL\Repl\unc\REPLICATION_NAM E_Tabl3d0a0ad\20040125190012\'. Is this the information you were looking for? When I was finished checking these things I attempted to restart the agent again (using sa)and got a similair message except the directory name it was complaining about changed to '\\SERVER_NAME\C$\MSSQL\Repl\unc\REPLICATION_NAME_Tabl3d 0a0ad\20040127190011\'. I also noticed that the directory it complained about the first time no longer exists! Does that make any sense? I appreciate your help. :) Thanks, John [quoted text, click to view] >-----Original Message-----
>Hi John, > >Thank you for using the newsgroup and it is my pleasure to help you with >you issue. > >As my understanding of you problem, you notice that the Distribution Clean >Up Agent is not running and you started it manually but encountered error >like: > >'Executed as user: 'ACCOUNT_NAME'. Could not remove directory >'\\SERVER_NAME\C$\MSSQL\Repl\unc\REPLICATION_NAME_Tabl3d0a 0ad\20040125190012 >\'. Check the security context of xp_cmdshell and close other processes >that may be accessing the directory. [SQLSTATE 42000] (Error 20015) >Associated statement is not prepared [SQLSTATE HY007] (Error 0) >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.' > >Right? > >You are right that the Distribution Clean Up Agent created automatically >when replication was setup. It is among the the Miscellaneous Agents folder >in Replication Monitor, they are to clean up and monitor different >replication processes. Every 10 minutes, the Distribution Cleanup Agent >removes replicated commands from the distribution database (default). The >Agent also removes all snapshots that have already been applied to the >Subscriber from the snapshot folder and that are older than 72 hours >(default). Running this Agent ensures that there is space for new snapshot >files. It will using using sp_msadd_mergereplcommands which calls >xp_cmdshell. > >You could right-click the Distributor Clean Up Agent and choose the 'Agent >History' to the history of the activity of it. From the information you >provided and the error message, I wonder: >1) You should check if there are any other process that is using the >directory, you could use FileMon from: >http://www.sysinternals.com/ntw2k/source/filemon.shtml >2) Confirm that this account you start the agent have the permission to run >'xp_cmdshell', when login Query Analyzer with this account, try to run >'exec xp_cmdshell 'dir c:\' >3) Confirm that the the account you start the agent have full control over >the directory: >\\SERVER_NAME\C$\MSSQL\Repl\unc\REPLICATION_NAME_Tabl3d0a0 ad\20040125190012\ > >Hope this helps and I am looking forward to you resply! Thanks > > >Best regards > >Baisong Wei >Microsoft Online Support >---------------------------------------------------- >Get Secure! - www.microsoft.com/security >This posting is provided "as is" with no warranties and confers no rights. >Please reply to newsgroups only. Thanks. > > > >.
Hi John,
Thank you for you feedback. Well, since no other processes are using the directory, the possiblity of this factor could be ignored. The 'sa' could be the owner of the agent owner, or the job owner of the clean up job ( you could unfolder the 'Replication Monitor'->'Agent'->' Miscellaneous Agents', right-click the 'distribution clean up: distribution', choose 'agent properties' and check it in the 'General' tab; Or you could unfolder 'Management'->'SQL Server Agent'->'Job', right-click the job 'distribution clean up: distribution' and in the 'General' tab, checking the owner). However, as I mentioned before, that it will finally run 'xp_cmdshell' to delete the directory, you should check the account which start the SQL Server Agent. You could get this information by right-click the 'SQL Server Agent' in the 'Management' and choose 'Properties', in the 'General' tab, you will see the Start Service Account. Please confirm this account could run 'xp_cmdshell' to READ and WRITE the directory instead of the job owner. Also, could you provide the agent 'Distribution Clean Up: distribution''s history by right-click it and choose the 'Agent History'? Looking forward to your reply! Thanks Best regards Baisong Wei Microsoft Online Support ---------------------------------------------------- Get Secure! - www.microsoft.com/security This posting is provided "as is" with no warranties and confers no rights. Please reply to newsgroups only. Thanks.
Hi Baisong,
Here is the latest message from the agent history (it looks like its the same as last time except the name of the folder at the end has changed: Executed as user: '@LOCAL_ACCT'. Could not remove directory '\\SERVER_NAME\C$\MSSQL\Repl\unc\REPLICATION_NAME _Tabl3d0a0ad\20040128210012\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015) Associated statement is not prepared [SQLSTATE HY007] (Error 0) 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 double checked to see which account is the owner of the agent by: 1) In Enterprise Manager...opening 'Replication Monitor'- [quoted text, click to view] >'Agent'->' Miscellaneous Agents' 2) right-clicking 'distribution clean up: distribution'3) choosing 'agent properties' 4) checking owner under the general tab The owner of the agent is 'sa'. Next, I double checked to see which account runs SQL Server Agent by: 1) In Enterprise Manager...opening 'Management' 2) right-clicking 'SQL Server Agent' 3) choosing 'properties' 4) under the 'general' tab...I can see the service startup account The service startup account is a local windows account. [Lets refer to the local account as "@LOCAL_ACCT" as to not use the real name in the forum :)] How do I log into Query Analyzer using "@LOCAL_ACCT" in order to test if xp_cmdshell can READ and WRITE to that directory? I know how to do this using a SQL Server authenticated login...but not as a windows account... I look forward to hearing back from you... Thanks, John [quoted text, click to view] >-----Original Message-----
>Hi John, > >Thank you for you feedback. > >Well, since no other processes are using the directory, the possiblity of >this factor could be ignored. The 'sa' could be the owner of the agent >owner, or the job owner of the clean up job ( you could unfolder the >'Replication Monitor'->'Agent'->' Miscellaneous Agents', right-click the >'distribution clean up: distribution', choose 'agent properties' and check >it in the 'General' tab; Or you could unfolder 'Management'->'SQL Server >Agent'->'Job', right-click the job 'distribution clean up: distribution' >and in the 'General' tab, checking the owner). However, as I mentioned >before, that it will finally run 'xp_cmdshell' to delete the directory, you >should check the account which start the SQL Server Agent. > >You could get this information by right-click the 'SQL Server Agent' in the >'Management' and choose 'Properties', in the 'General' tab, you will see >the Start Service Account. Please confirm this account could run >'xp_cmdshell' to READ and WRITE the directory instead of the job owner. >Also, could you provide the agent 'Distribution Clean Up: distribution''s >history by right-click it and choose the 'Agent History'? > >Looking forward to your reply! Thanks > >Best regards > >Baisong Wei >Microsoft Online Support >---------------------------------------------------- >Get Secure! - www.microsoft.com/security >This posting is provided "as is" with no warranties and confers no rights. >Please reply to newsgroups only. Thanks. > > >.
Hi John,
Thank you for you post. Could you please provide the job history? If the start service account is running, you will see the steps information of how the job runs ( Note that the 'Distribution clean up: Distribution' is actually a job). Please right-click the 'Distribution clean up: Distribution' Agent in the 'Replication Monitor'->'Agents'->'Miscellenous Agents', and choose 'Agent History'. You would see information of the job steps, and see the message like 'The job succeeded. The Job was invoked by user <DomainName\UserName>. The last step to run was step 1(Run agent.).' ( Note the <DomainName\UserName> could be a <MachineName\UserName> as a local account). If you could see the history of the 'Distribution clean up: Distribution'. Could you please provide the history and if the job ran successful, the job is invoked by which user? There are two method to start the SQL Server Agent Service, if the job owner is within SQL Server system administration role, all the job will run by the Agent Start Service account. If it is not within the SQL Server system administration role, you can add it by unfolder 'Management', right 'SQL Server Agent', choose 'properties', in the 'job system' tab, in the 'Non-SysAdmin job step proxy account', uncheck the and enter the password For the error message, could you please provide the information of which account you use to login into you windows, then open the Enterprise Manager, the manually start the 'Distribution clean up: Distribution' agent and get the error? As for the service start account you get from you post, you could login into you windows, open Query Analyzer by 'windows authentication' and run 'xp_cmdshell' (exec xp_cmdshell 'dir c:\') to check if it has the permission to run it. If you want to know clear what happened in details, please use profiler to catch which T-SQL statement is running and by who. Looking forward to your response. Best regards Baisong Wei Microsoft Online Support ---------------------------------------------------- Get Secure! - www.microsoft.com/security This posting is provided "as is" with no warranties and confers no rights. Please reply to newsgroups only. Thanks.
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |