Cannot Shrink Logs on a database with Replication.

Question

Hi,

I have a SQL 2019 Enterprise instance with a database that is about a TB in size.
I have 2 publications that replicate to the database (Transactional Replication). These are inactive as there is nothing writing to the source database (Source is a DB2 iSeries).
The database has a log file of 3TB and it wont let me shrink it. I have tried the following:

Backing up Transaction log File
Shrinking the Log file
Setting the db to Simple mode and then shrinking

None of this works.

Any ideas on the correct way to shrink when there is replication?

Thanks,

Zoe :)

Answer 1

Hi,

I have 2 publications that replicate to the database (Transactional Replication).

I think that you confuse a bit. In a very simple for non experts: The publisher is the source which needs to replicate data, the Distributor stores the replicated data temporarily, and the subscribers are the destination database where we replicate the data to,

I highly recommend to go over this very short document:
https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/replication-publishing-model-overview

The transaction log file is build from small virtual log files which names VLF in short. Every time the log growth it means that new VLF is added. When there are inactive VLFs, then the server can use them once the current VLF is full. When the server can’t make any VLFs inactive, then it records the reason why, and that’s what you get from the sys.database in the column log_reuse_wait_desc. In your case, there are records in the log file (in the VLF) which are waiting to be replicated, which is why the server do not delete these (do not mark the VLF as inactive) in your case.
For more accurate and full information please check this doc: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide

(1) Make sure that the transaction replication Log Reader Agent job is not disabled

(2) You can control how often the Log Reader Agent job is queried for replicated transactions (default is 5 seconds). Make sure that the interval fit the amount of transactions than you have. You can use the stored procedure sp_replcounters in order to monitor the latency, throughput, and transaction count for each published database. The Replicated transactions column provide the number of transactions in the log awaiting delivery to the distribution database. Check this for more insights about your scenario.

More information can be found in this nice post: https://www.mssqltips.com/sqlservertip/3598/troubleshooting-transactional-replication-latency-issues-in-sql-server/

I have tried the following: Backing up Transaction log File, Shrinking the Log file, Setting the db to Simple mode and then shrinking

Backup will not change the status of the records which wait for replication, shrinking can only remove inactive VLF from the transaction log file but as I explain above your VLF are not inactive since they include records which wait for replica, and setting for simple is awful idea which break the backup chain and will not change anything regarding the records waiting for replica.

Any ideas on the correct way to shrink when there is replication?

You ask how to do something which you should not do! If you want to keep the replica active, then you need the records which are waiting to be replicated.

In general, shrinking log file is a bad idea and not an action which should be done (except very very... very extreme cases). There is a reason why the log file to growth to this size!

Check the two points I mentioned above as first steps.

If you want to remove replication then the records which were not replicated will not be needed anymore and the server will be able to remove them. IN theory, in extreme cases you can remove the replication and start it over from scratch, but this is not part of maintenance plan.

This doc can help you this un-recommended task: https://learn.microsoft.com/en-us/troubleshoot/sql/replication/manually-remove-replication

Answer 2

What do log reuse wait reports for the database?

select name, log_reuse_wait_desc
from sys.databases
order by name

Answer 3

So apparently there is a lot in the log file waiting to be replicated...

I am not sure that I understand this:

I have 2 publications that replicate to the database (Transactional Replication). These are inactive as there is nothing writing to the source database (Source is a DB2 iSeries).

If it is a publication, how can there be a source table elsewhere?

Answer 4

They say 'REPLICATION' For the DB that I cannot shrink

Answer 5

Hi @zoe Ohara ,

The result of log_reuse_wait_desc is Replication. This means that the SQL server transactions are waiting to be replicated, the VLFs are active, so you can not shrink the log file. You need to wait for the transactions be replicated completely. If you waiting this for a long time, you need to find the reason that why the replication has latency. The latency cause the transactions did not be replicated, VLFs still active, then log file could not be shrank.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".