I have an issue with transactional replication which leads to running out of disk space.
The scenario is following.
- Database
A
is replicated to another computer via local distributor with command retention set to 24 hours (customer requirement). - Subscriber computer gets disconnected for a longer time.
- After few hours of high load undstributed commands are piling up on publisher computer causing lack of disk space.
At first I tried to limit distribution db size, but after it reached max size, the ldf file for database A
is swelling.
I wouldn't want to set limits on ldf file since it can cause database A
to stop functioning.
Is there any approach I could choose to prevent this situation? I don't care about replication failure, my primary concern is to keep the database available for clients.
The perfect option would be an automatic subcription expiration if reached given number of transactions or used given disk space.
Best Answer
Basically the problem is your subscriber going offline. If it remained online then the replicated data would be committed on the subscriber and there would be no problem.
The effect on transactional replication is described in the following link: https://technet.microsoft.com/en-us/library/ms151254(v=sql.105).aspx
The simplest answer is to get more disk space to support the potential log size.
EDIT: Clarification from your reply: The subscriber gets disconnected for a long period due to a failure of some sort.
If disk space cannot be made available to handle the unpublished data, then you basically need to delete the subscription. Once the outage is resolved, then you can restore and re-establish the replication subscription.