Sql-server – Prevent SQL Server publisher from running out of disk space

replicationsql servertransactional-replication

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.

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

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.