We currently have a transaction log thats been growing incrementally and has too many VLF files. I am going to follow a process of shrinking and then re-growing the log file to a suitable size to alleviate this problem.
My question is shrinking will only reduce from the end of the file to the last active portion of the log. Therefore should I change to simple recovery first, then perform the shrink?
Following this I will change back to full perform a full backup to start the backup chain again.
Best Answer
No, do not switch to SIMPLE recovery model since that will result in a break in your backup chain. If you are doing in FULL recovery model, do the following:
Repeat until you shrink the log file as small as possible. (My experience is that two or three times is usually needed.)
Then set the preferred default log size and the increment of growth for the log. Do not use percentage log growth, but have a defined increment.
If you follow this, then the backup chain is preserved.
If there are reasons that you need to switch to SIMPLE recovery model, then once you return to FULL recovery model you should BACKUP DATABASE to reestablish the start point for your following log backups.