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:
Date1/21/2010 10:00:00 AM
LogJob History (Distribution clean up: distribution)
Step ID1
ServerSQL2
Job NameDistribution clean up: distribution
Step NameRun agent.
Duration00:44:59
Sql Severity13
Sql Message ID1205
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
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
December 9, 2023 at 6:29 am
Hello mate.
This query is too slow when your commands is too much in dbo.MSrepl_commands. I suggest you to use this query I wrote.
you're be able to customize this with any "Where Clause" you want.
;WITH cte AS (
SELECT MSrepl_transactions.xact_seqno,MSrepl_transactions.publisher_database_id,article_id,COUNT(dbo.MSrepl_commands.xact_seqno) countXact FROM dbo.MSrepl_transactions
INNER JOIN dbo.MSrepl_commands ON MSrepl_commands.publisher_database_id = MSrepl_transactions.publisher_database_id AND MSrepl_commands.xact_seqno = MSrepl_transactions.xact_seqno
WHERE
--===Your Conditions===
--CAST(entry_time AS DATE )>='2023-12-07'
--article_id IN ( 58,61 )
--AND MSrepl_transactions.xact_seqno = 0x0016CE2200003381001200000000
GROUP BY MSrepl_transactions.xact_seqno
, MSrepl_transactions.publisher_database_id
, article_id
)
,cte2 AS (SELECT s.agent_id,s.article_id,s.publisher_database_id,
UndelivCmdsInDistDB=SUM(CASE WHEN t.xact_seqno > h.maxseq THEN t.countXact ELSE 0 END),
DelivCmdsInDistDB=SUM(CASE WHEN t.xact_seqno <= h.maxseq THEN t.countXact ELSE 0 END)
FROM MSsubscriptions s
INNER JOIN cte t ON t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id
INNER JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM MSdistribution_history GROUP BY agent_id) as h
ON (h.agent_id=s.agent_id)
GROUP BY s.agent_id
, s.article_id
, s.publisher_database_id
)
SELECT s.agent_id
, name
, s.article_id
, article
, s.publisher_database_id
, SUM(s.UndelivCmdsInDistDB) UndelivCmdsInDistDB
, SUM(s.DelivCmdsInDistDB) DelivCmdsInDistDB FROM cte2 s
INNER JOIN dbo.MSdistribution_agents ON id = s.agent_id
INNER JOIN dbo.MSpublications ON MSpublications.publication = MSdistribution_agents.publication
INNER JOIN dbo.MSarticles ON MSarticles.article_id = s.article_id AND MSarticles.publication_id = MSpublications.publication_id
WHERE
--======YourConditions======
--s.UndelivCmdsInDistDB >0
--AND name LIKE '%for%'
GROUP BY s.agent_id
, name
, s.publisher_database_id
, s.article_id
, article
ORDER BY SUM(s.UndelivCmdsInDistDB) Desc
December 9, 2023 at 6:35 am
Hello Mate
This query is too slow when your commands is too much in dbo.MSrepl_commands. I suggest you to use this query I wrote.
you're be able to customize this with any "Where Clause" you want. It's 20 Times faster than "Select * From dbo.MSrepl_commands" 😉
;WITH cte AS (
SELECT MSrepl_transactions.xact_seqno,MSrepl_transactions.publisher_database_id,article_id,COUNT(dbo.MSrepl_commands.xact_seqno) countXact FROM dbo.MSrepl_transactions
INNER JOIN dbo.MSrepl_commands ON MSrepl_commands.publisher_database_id = MSrepl_transactions.publisher_database_id AND MSrepl_commands.xact_seqno = MSrepl_transactions.xact_seqno
WHERE
--=============Your Conditions=============
--CAST(entry_time AS DATE )>='2023-12-07'
--article_id IN ( 58,61 )
--AND MSrepl_transactions.xact_seqno = 0x0016CE2200003381001200000000
GROUP BY MSrepl_transactions.xact_seqno
, MSrepl_transactions.publisher_database_id
, article_id
)
,cte2 AS (SELECT s.agent_id,s.article_id,s.publisher_database_id,
UndelivCmdsInDistDB=SUM(CASE WHEN t.xact_seqno > h.maxseq THEN t.countXact ELSE 0 END),
DelivCmdsInDistDB=SUM(CASE WHEN t.xact_seqno <= h.maxseq THEN t.countXact ELSE 0 END)
FROM MSsubscriptions s
INNER JOIN cte t ON t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id
INNER JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM MSdistribution_history GROUP BY agent_id) as h
ON (h.agent_id=s.agent_id)
GROUP BY s.agent_id
, s.article_id
, s.publisher_database_id
)
SELECT s.agent_id
, name
, s.article_id
, article
, s.publisher_database_id
, SUM(s.UndelivCmdsInDistDB) UndelivCmdsInDistDB
, SUM(s.DelivCmdsInDistDB) DelivCmdsInDistDB FROM cte2 s
INNER JOIN dbo.MSdistribution_agents ON id = s.agent_id
INNER JOIN dbo.MSpublications ON MSpublications.publication = MSdistribution_agents.publication
INNER JOIN dbo.MSarticles ON MSarticles.article_id = s.article_id AND MSarticles.publication_id = MSpublications.publication_id
WHERE
--=============Your Conditions==============
--s.UndelivCmdsInDistDB >0
--AND name LIKE '%for%'
GROUP BY s.agent_id
, name
, s.publisher_database_id
, s.article_id
, article
ORDER BY SUM(s.UndelivCmdsInDistDB) DESC
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply