What happened?

Question

What happened?

Salves 501

Hi,

yesterday we received a warning from the monitoring that the disks of one of our bases was full and when we looked, we noticed that the sizes of the primary and secondary LOGs were different.

We looked at the Always On LOGs and found nothing.

We looked at the system LOGs and found nothing.

We looked at the cluster LOGs and found nothing.

When something like this happened, or because our secondary replica with Azure was down, then it caused the LOGs to grow but that didn't happen now.

Thanks.

Seeya Xi-MSFT 16,756 Reputation points

2022-06-13T09:27:49.663+00:00

Hi @Salves ,

In order to benefit all community members who are having this similar issue, please choose an answer to accept or vote for the answer you think is useful to you. Your contribution is highly appreciated.

Best regards,
Seeya

2 answers

Your answer

Seeya Xi-MSFT 16,756 Reputation points

2022-06-13T09:27:49.663+00:00

Hi @Salves ,

In order to benefit all community members who are having this similar issue, please choose an answer to accept or vote for the answer you think is useful to you. Your contribution is highly appreciated.

Best regards,
Seeya

Answer 1

Tom Phillips 17,786

The physical size of the log file really has no meaning. There is no relationship between the log size on the primary and the secondary. This is not a problem or an indication of a problem.

Salves 501 Reputation points

2022-05-19T03:47:18.66+00:00

Hi @Tom Phillips ,

like this?

We have observed that the sizes are similar or even the same on a basis as transactions occur very quickly.

What seems to be happening and this is a concern is that the number of transactions that are executed in a database at this moment is very large and this is causing a big difference in the LOG bases and growing a lot.

Thanks.
Tom Phillips 17,786 Reputation points

2022-05-19T12:19:31.92+00:00

The log files are independent on the other servers. Just because they are different sizes does not mean anything.

The size of the log is dependent on the number and size the transactions between log backups. The physical size is how big the log had to be at one time to handle all the transactions. The data inside the file is marked for reuse after a log backup. Once allocated, the physical size of the log never shrinks, unless you manually shrink it.

You should be looking at the space available in the log, not the physical size of the log files. Comparing the physical size of the log between servers, has no meaning.

Answer 2

Seeya Xi-MSFT 16,756

Hi @Salves ,

Welcome to Microsoft Q&A!
First of all, there must be a full backup in Alwayson. Unlike the simple recovery model, the system does not automatic truncate the transaction log after each transaction. For databases with a full or bulk log recovery model, the system truncates the transaction log only after a successful backup of the transaction log.
For databases with frequent transaction log growth, you can establish a maintenance plan and back up the transaction log periodically, from a few minutes to an hour.

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Salves 501 Reputation points

2022-05-17T12:57:06.463+00:00
Hi @Seeya Xi-MSFT ,

we've had an Alwayon for a few years now working very well.

The base backup configuration is always full.

There is a full backup that runs every day at night.

There is a backup of transactions LOGs every 15 minutes.

There is a routine that cleans the full backup and logs every three days.

When a very large task occurs, it appears that replication between the secondaries waits for the task to finish and then frees up this space.

Our environment is very performant with all-flash disks and Giga network and I don't see saturation.

There is no configuration (redo_queue_rate) to increase this transfer rate of LOGs from the primary to the secondary.

Thanks.

Seeya Xi-MSFT 16,756

Hi @ salves,

You can refer to this script below:

USE [databasename]
DECLARE @bakfile nvarchar(100)
SET @bakfile='E:\dbbak\[databasename]_log_bak_'+CONVERT(nvarchar(8),GETDATE(),112)+'.log'
BACKUPLOG[databasename]TODISK= @bakfile WITHRETAINDAYS=1,COMPRESSION
DBCCSHRINKFILE(N'[databasename]_log',0)
GO

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.

Share via

What happened?

2 answers

Your answer