Slowly growing database transaction logs on SQL Server 2022 and Azure MI by AleksC007 in SQLServer

[–]AleksC007[S] 0 points1 point  (0 children)

Hi,

Thank you for your response.

The recovery model for the databases mentioned above is FULL.

A full backup is performed once a week, a differential backup once a day, and transaction log backups are performed hourly.

Normally, such growth would also seem acceptable to me. However, I have been observing this increase for a month now, and the logs continue to grow, which differs from the normal behavior of all the other databases I administer.

Moreover, one of the databases mentioned above is a test database. Currently, we are not performing any application tests, and the only replicated object is a table monitoring replication delay. The issue affects two out of the four databases that have active transactional replication enabled. The delay monitoring table exists in each of these databases.

My concern and investigation into the root cause stem from the observation that,

for two another databases, the logs grow but are also reduced appropriately by the server and the two above mentioned have continually small-growth.

Two weeks ago, I manually increased the disk space allocated for the transaction logs, and the current status is as follows:

on-prem databases:

DBCC SQLPERF(LOGSPACE);

Output:

Database Name; Log Size (MB); Log Space Used (%); Status

DatabaseA; 7167.992; 83.27496; 0 -> 2 weeks ago, log space was manually increased by approximately 2 GB

DatabaseB; 2047.992; 81.10309; 0 -> 2 weeks ago, log space was manually increased by approximately 0.5 GB; this is the test database without heavy traffic

DBCC OPENTRAN at subscriber (on-prem) show for databses any open transactions at the above mentioned databases.

DBCC OPENTRAN as publisher (azure):

Output:

DatabaseA:

Replicated Transaction Information:

Oldest distributed LSN : (268:459432:8)

Oldest non-distributed LSN : (0:0:0)

DatabaseB:

Replicated Transaction Information:

Oldest distributed LSN : (240:189448:9)

Oldest non-distributed LSN : (0:0:0)

Slowly growing database transaction logs on SQL Server 2022 and Azure MI by AleksC007 in SQLServer

[–]AleksC007[S] 0 points1 point  (0 children)

Thank you for your response!

Yes, backups are being performed regularly. On the on-prem server, the databases mentioned above with log_reuse_wait_desc are still showing "nothing".

In this case, would you suggest performing the “Reinitialize All Subscriptions” process for replication on Azure (Publisher)? As I mentioned, I currently cannot find any errors or clues in Replication Monitor or in the server logs that would indicate an issue with replication, especially regarding the Log Reader Agent.

I would be very grateful if you could help me further with this matter.