January 21, 2010 at 9:05 am
HI Experts
I am having problem with Replication in sql 2005. My Distribution job failing every time with following Error. and my distribution database is growing and about to occupying entire Drive. its grown up to 88 GB and I have only 20 GB free space . I am trying to clean it but it not working ..
Any suggestion how to trouble shoot.
Error Information:
Date 1/21/2010 10:00:00 AM
Log Job History (Distribution clean up: distribution)
Step ID 1
Server SQL2
Job Name Distribution clean up: distribution
Step Name Run agent.
Duration 00:44:59
Sql Severity 13
Sql Message ID 1205
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: Ginka\SQLSVRAGENT. Transaction (Process ID 217) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
Appreciate your quick response.
Thanks
ichbin
January 21, 2010 at 12:09 pm
Is it deadlocking with the distribution agent? Try running this and see how many undelivered commands there are compared to delivered.
SELECT * FROM distribution.dbo.MSdistribution_status
It might end up being faster to stop SQL Agent and do the cleanup manually if you've got a lot of delivered commands waiting. You may also need to adjust the schedule of how often the cleanup job runs once the manual clean is over.
This link will take you to a post about manual cleanup.
http://blogs.technet.com/claudia_silva/archive/2009/05/04/replication-distribution-cleanup.aspx
Considering your pressing space concerns, I'd probably stop SQL Agent anyway for the time being and get the cleanup done. You can then start to properly troubleshoot again afterward when you're not worried about running out of space.
January 21, 2010 at 1:02 pm
HI .. I am running SELECT * FROM distribution.dbo.MSdistribution_status
query it’s still running ..
Stopping Agent in Production server its little hard... I have other import jobs running on agent for every 10 mins .. so I cannot stop the agent..
I am reading some articles saying .. stop the log reader agents for all the subscriptions and run this cleaning job ...
What is manual process of cleaning without stopping SQL Agent ?
Thanks for your information
-ichbin
January 21, 2010 at 2:23 pm
ichbinraj (1/21/2010)
HI .. I am running SELECT * FROM distribution.dbo.MSdistribution_statusquery it’s still running ..
Is it being blocked? If so, can you tell which process it is?
Stopping Agent in Production server its little hard... I have other import jobs running on agent for every 10 mins .. so I cannot stop the agent..
I am reading some articles saying .. stop the log reader agents for all the subscriptions and run this cleaning job ...
What is manual process of cleaning without stopping SQL Agent ?
You can try the suggestions of stopping the log reader agents. You can also try stopping synchronization of the subscription and running the cleanup process manually.
January 21, 2010 at 2:35 pm
Hi I was running that query and I got some time outs to end users so .. I have canceled the query ...
what else we can do to prevent this problem
Thanks
ichbin
January 21, 2010 at 2:46 pm
I think you're going to just have to stop the log readers or synchronizing and move forward with the cleanup steps without checking the MSdistribution_status
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply