How to reduce the VLFs

Question

How to reduce the VLFs

Sam 1,476

Hi All,

We have a database with 5TB size and has VLF near to 900.

Transaction log file size 700GB.

We are on SQL Server 2017 EE Always on availability group.

During restores/db refreshes and during AG fail over times it is taking 30-45 mins to bring the database online.

Question is how to reduce the VLFs? I don't want to remove the database from AG and if we have to resize the log file with MINIMAL DOWN TIME, then what are the precautions needs to be taken?

Regards,

Sam

Dirk Hondong 871 Reputation points

2024-03-19T15:48:55.28+00:00

Hi Sam,

I think a VLF count of around 900 for a 700GB Tlog file is not that bad at all. but to get an idea of what needs to be done , then take a look here: https://forums.sqlteam.com/t/data-file-and-log-file-size-in-sql-server-always-on/21886/11

The change you are planning can be done without taking the DB out of the AG.

But: shrinking and regrowing the log file can cause blocking.

You can also read this article: https://www.seangallardy.com/availability-group-long-failover-times/

You can also take a look at the sizes of the VLFs, maybe a few of them have an unusual size

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-info-transact-sql?view=sql-server-2017

1 answer

Your answer

Dirk Hondong 871 Reputation points

2024-03-19T15:48:55.28+00:00

Hi Sam,

I think a VLF count of around 900 for a 700GB Tlog file is not that bad at all. but to get an idea of what needs to be done , then take a look here: https://forums.sqlteam.com/t/data-file-and-log-file-size-in-sql-server-always-on/21886/11

The change you are planning can be done without taking the DB out of the AG.

But: shrinking and regrowing the log file can cause blocking.

You can also read this article: https://www.seangallardy.com/availability-group-long-failover-times/

You can also take a look at the sizes of the VLFs, maybe a few of them have an unusual size

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-info-transact-sql?view=sql-server-2017

Answer 1

Hi @Samantha r

First you need to determine if you have a high VLF count.

Then fix a database with a high VLF count in below process:

Check the current size of the transaction log.
Backup the transaction log.
Shrink the transaction log to as close to 0 KB as possible.
Check that the VLFs are reduced to a lower number.
Grow the transaction log back out to the original size.

See this blog for more details.

Best regards,

Cosmog Hong

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".

Share via

How to reduce the VLFs

1 answer

Your answer