September 20, 2018 at 6:51 am
Hi All,
We have a Database that is in Availability group. This is a busy DB. We did a Reindexing and that made the log file grow upto 400GB. (DB is just 600GB and the log file was previously 100GB with Min size set for 100GB as well). We have TRN logs running every 5 minutes on this DB.
My problem is I have 429 VLFs and out of that 128 VLFs with Status 2 and rest are status 0. I want to bring the Log file back to around 100 GB in size. I do not think we need to have a Log file as big as the Data file. Shrink file wont help.
Anyone has any idea how to achieve this without having to take it out of Availability group? I know that there are issues with AG and reindexing but I dod not think it would cause this much issue for me.
-Roy
September 20, 2018 at 12:47 pm
I did this a few weeks back. It might be worth taking a look at your autogrowth settings for the log file. I typically set mine to 128mb to 256mb vs the default 10%.
If you take a log backup and immediately shrink the log you should be successful without having to pull out of the AG go simple recovery, shrink go back to full, rejoin AG and reseed.
Pinal has a pretty good write up on this. Just remember to take a log backup right before the shrink.
September 20, 2018 at 1:20 pm
There is nothing wrong with having a large transaction log file - and you will need that size when you decide to reindex again. The number of VLF files doesn't seem to be concerning either - I have a 300GB transaction log file for a large database that has 616 VLF's. That makes each VLF around 500MB...
If anything - I would say you don't have enough VLF's for a 400GB transaction log and that each VLF is much larger than is needed.
You definitely need to look at the autogrowth settings and review the changes to SQL Server 2016 and how the VLF's are actually created. The algorithm has changed and once you get to a certain percentage size growth - you get 1 VLF per autogrowth instead of the 16 (or 8).
If you do want to shrink - then issue the shrink file, perform a transaction log backup - perform the shrink again...repeat until you get down to the size you want it to be...this works regardless of whether or not you are setup in an AG.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply