SQL SERVER 2019 STD AG group filling transaction log up

Question

Hi

I have a problem with our High Availability environment, since we have upgraded the servers to Windows 2022 datacentre version, Using WSFC the High Availability seems to continually holding the transaction log file open, it grew to 150gb, which I needed to remove from AG and reduce down as lack of disk space. I have added a smaller database to AG and can see the same scenario happen were the log file will continue to grow and not be reused.

If I try to do a manual shrink after a log backup I get

Cannot shrink log file 2 (DBATOOLS_log) because the logical log file located at the end of the file is in use.

and SELECT [name] AS DATABASE_NAME , log_reuse_wait_desc

FROM sys.databases

still shows LOG_BACKUP

Answer 1

@Clive Wightman ,

In an AG there are known side effects with the log. Sometimes all the VLFs will show 2 for the status, and sometimes you can get it to clear with a CHECKPOINT command.

But nonetheless, there are only a couple of reasons why the log grows when you think it shouldn't. When you have an AG in either sync or async mode, you can have a transaction that is large enough to fill the log and force the log to grow. In this case you will need to have more space available.

Have you gone into the Dashboard and add columns Log Send Queue Size and Log Redo Queue Size. These queues should be small, but if they are not then it is a matter of the log not truncating because it has not been redone on the secondary side.

It is important to understand how your AG is behaving so it is important to check the DMVs related to availability groups and it looks like one of them said that the AG is healthy. Lots of things can make the log misbehave in an AG, but knowing what to do about it is probably harder than it should be.

If you have any more information that would help, reply this answer.

Answer 2

Hi @Clive Wightman ,

Thank you for your reaching out and welcome to Microsoft Q&A!

it grew to 150gb, which I needed to remove from AG and reduce down as lack of disk space.

For this issue, I get the information below from this thread:

1. Get rid of the maintenance plan for rebuilding indexes. It's basically useless, a waste of resources, and will contribute to your Transaction Log growing which is your main concern.
2. Backups are somewhat resource intensive, but can generally be ran concurrently with other queries and processes for systems that aren't under high I/O load (especially consistently ran Transaction Log backups). You didn't mention the type of disk your database is on, but if it's an NVMe or SSD, and the disk you're backing up to is of similar caliber, then that'll definitely be helpful.
3. If a 60 GB database generated only 300 GB of Transaction Logs over 4 years, then I doubt your database is super transactional (aka not too heavy on the I/O side of things). So you might do ok with running your backups.
4. Definitely pick the best-off hours' time you can run a Full Backup and then a Transaction Log backup right after. And fix those maintenance plans / jobs so they can run consistently.
5. The more frequently you run Transaction Log backups, the less data it'll need to backup at one time and ergo will run faster. But of course at the tradeoff of resources being consumed to do the backups more frequently. Again, I don't think your database is super transactional, and it's pretty tiny in size, so your Transaction Log backups should normally be quick (ideally less than a second) when ran consistently. Another bonus is you get more granular points of recovery by taking them more frequently.
6. If possible, find out how long it took that other user to run the Full backup so you have an idea going into it. The Transaction Log backup will probably roughly take 5x as long (300 GB = 5 * 60 GB).

You can get more detailed information from this thread, hope this can help you well.

In addition, please refer to this article, it shows us the solution in how to resolve SQL Server Transaction Log Grows And Fills Up Drive.

If I try to do a manual shrink after a log backup I get Cannot shrink log file 2 (DBATOOLS_log) because the logical log file located at the end of the file is in use.

For this error message, this means active VLF and can’t be released. At this time, the log cannot be shrunk because the virtual log is used for restoring operations, and its space can only be freed up if the log is backed up or truncated. You can find another disk and add a second log file on this disk. When creating a database, specify that the LDF file can be larger, for example, if it is greater than 1G, the LDF file will automatically grow by 200 MB at a time.

Alternatively, you can change the recovery model and try to shrink it a few times again.

Feel free to share your issues here if you have any concerns.

Best regards,

Lucy Chen

---

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.

https://docs.microsoft.com/en-us/answers/support/email-notifications