April 7, 2011 at 11:31 pm
Hi,
I know this is a frequent topic, and I see many things about it however I don't see something that matches my exact problem :S
(Also I am not a DBA, but all the same I got harpooned into setting it up...) 😉
I have a transactional replication setup between on publisher and two subscribers for two databases.
It seems to work nicely. Lately there were some errors in the transaction due to no space (due to the two database subscriptions running at the same time). One job was moved and issue solved. However, I have a distribution database which is sized at around 112 GB :crazy:
[Setup]
Windows 2003 x64 EE 2 node cluster with SP2 installed.
SQL 2005 SP3 with roll up 10 (if I remember correctly) Build in the SQL server is: 9.00.4273.00
For various reasons (mainly developers) we can not upgrade to SP4 yet...
[Steps checked]
I would like to get that size down. I have checked that the replication services are running (permanently).
I have checked that the publication job runs successfully every morning and also the subscribers run correctly.
I have checked that the distribution clean up job runs as it should (manually and automatically), which it does.
Am I missing something (probably yes) and if so, does anybody have a suggestion to how I can do something about this database in a safe manner?
//Martin
April 8, 2011 at 1:53 pm
Have you checked the space used? If there is plenty of empty space there is no reason why you cannot shrink the database to a more reasonable size. I'd recommend shutting down all log reader and distribution agents first.
Also you can run this statement to give you an idea of how many commands are left to deliver.
----- Undelivered and Delivered commands for each article
SELECT ag.name
, ag.publication
, a.article
, s.UndelivCmdsInDistDB
, s.delivCmdsInDistDB
FROM dbo.MSdistribution_status s
INNER JOIN dbo.msdistribution_agents ag ON ag.id = s.agent_id
INNER JOIN dbo.msarticles a ON a.publisher_id = ag.publisher_id AND a.article_id = s.article_id
--ORDER BY ag.publication
--WHERE UndelivCmdsInDistDB > 0
--where publication = 'Extract_Base'
ORDER BY
s.UndelivCmdsInDistDB DESC
,ag.publication
April 8, 2011 at 10:51 pm
Hi,
thanks for the reply, I forgot to mention that, sorry. I tried shrinking the DB already, but it states that I only have around 2 MB available in the db file.
So it seems that it actually is keeping the data in the distribution db, despite the fact that it should release it after the replication (If I understand it correctly from other threads) 🙂
One other thing worth mentioning:
Since my change in the time of the replication earlier this week, the jobs finish successfully and the database no longer appears to grow.
This to me seems like the cleanup job is working, but for some reason "random data" for some reason has been kept.
I will try your script during the weekend since I'll be near the database and let you know.
//Martin
April 11, 2011 at 2:53 am
Hi,
I have stopped the agents and I am running the script.
This has been running for some time now, (~1 hour).
Is this normal considering that the database is around 112 GB?
//Martin
April 11, 2011 at 4:14 am
As it is taking so long, it suggests there are several million undelivered transactions. This is normally due to an unreachable subscriber or an error preventing deliverly.
If there are any errors, replication will stop delivering and the commands will queue up for delivery.
April 11, 2011 at 5:11 am
Hi Jimbo,
thank you for the answer.
Is there a way around this?
- reinitialize subscriptions?
- dump tables?
- Remove and re-created the replication?
//Martin
April 11, 2011 at 5:51 am
You generally wont need to do anything too drastic. this is what I would do.
1. Confim the status of each subscriber in Replication monitor. Be careful not to assume a Green Tick means OK. It could mean everything is working but the subscriber has expired.
2. Use the previously supplied query to establish what commands havent been delivered.
What I would expect to see is a failed subscriber. Either a command has failed to be delived or it isnt contactable.
The last resort would be to reinitialise each subscriber and clear out the distribution commands.
April 11, 2011 at 7:26 am
Hi Jimbo,
thanks! 🙂 Will try this.
//Martin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply