Resolving high VLF count when database is part of an Availability Group

Question

(SQL 2014 Ent Ed.) - do I need to break my Availability Group if I need to reduce the number of VLFs in a database that is participating in an AG? Does anyone have the process documented? I am thinking the process goes something like this:

1. Remove secondary nodes from the AG
2. Delete the database from the secondary nodes
3. Remove the database from the primary node of AG
4. Shrink the log file and regrow to proper size
5. Add the database back to the AG
6. Add the secondary nodes back to the AG and allow to sync

Thank you for any direction you can help me with!

Answer 1

I just tested this on a SQL Server 2012 database that is in an availability group, and the log shrink is replicated to the secondary node(s). Accordingly, you can simply shrink log files and then resize them as you would on a database that is not in an availability group.

Answer 2

Since DBCC log shrinks are technically "writes" they will be synchronized over to the secondary server. So you only need to perform step #4, i.e. shrink your logs.

Since shrinking is generally not something you want to do all willy nilly, also take heed of the below if you haven't yet:

Your VLFs will eventually increase again:

• If the file growth is up to 64MB the new portion of the log file will contain 4 VLFs
• If the file growth is more than 64MB and up to 1GB the new portion of the log file will contain 8 VLFs
• If the file growth larger than 1GB = 16VLFs

So make sure that you're not doing this in vain based on the projected VLF growth for your end goal log size. To make sure you're being sane about VLF counts, you can also consult Transaction Log VLFs - Too many or too few which has a great breakdown of information.