2

I have an issue with transactional replication which leads to running out of disk space. The scenario is following.

  1. Database A is replicated to another computer via local distributor with command retention set to 24 hours (customer requirement).
  2. Subscriber computer gets disconnected for a longer time.
  3. 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.

Improve this question
1
  • Disk space can be cheap: isn't adding more disk space an option?
    – spaghettidba
    Nov 2, 2015 at 15:39

2 Answers 2

Reset to default
2

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

For each database that will be published using transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database might require more space than the log of an identical unpublished database, because the log records are not truncated until they have been moved to the distribution database.

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.

Improve this answer
2
  • Thanks. Normally subscriber will be available all the time, but in case of hardware failure it can take a longer time to fix it. The replication here is in fact two-way for high availability purposes but I don't mind replication failure since it can always be reinitialized as long as the primary db is working.
    – nosalan
    Nov 2, 2015 at 16:06
  • Decide how long of a retention period you need, and plan disk space around that requirement. Balance that with the work required to reconfigure replication.
    – Hannah Vernon ♦
    Nov 2, 2015 at 22:29
0

If you don't have a strong connection to subscribers, transnational replication isn't a good choice, I would suggest using Merge Replication, as it is more suitable for situations like this. Merge Replication also sends less data (just final changes and not all changes)and doesn't use distribution database for replicated data. and as it mentions here: https://msdn.microsoft.com/en-us/library/ms151740.aspx

Replication functions properly using any of the recovery models: simple, bulk-logged, or full. Merge replication tracks change by storing information in metadata tables. Transactional replication tracks changes by marking the transaction log, but this marking process is not affected by the recovery model.

transnational Replication is more dependent to transaction log.

you may also want to take a look at https://msdn.microsoft.com/en-us/library/ms152746.aspx

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.