Hi Kenneth, awesome article, thanks for sharing!
One question if I may... You mention reducing the amount of VLFs. Is that related to a specific wait? I know you could come to that conclusion if it takes a long time to restore a small database for example, but is it something you can deduce from wait stats related DMV's as well?
Thx
Thierry
High number of VLFs are mostly caused by incorrect settings in the log file AUTOGROWTH value. When the AUTOGROWTH is small of specified as a percentage, it is likely that growths will be happening often and one might experience WRITE_LOG or similar wait events. I did not deduce the need to correct the configuration from a wait event in this case however but it is a basic practice I adopt to eliminate problems in this area. An YES in fact the backup time was also quite poor when we tried to relocate the databases involved in this case. It was corrected once we changed the autogrowth setting, shrunk the log and allocated a good amount as initial size for the transaction log file. (Credits to Glen Berry)
Ok, thanks. I was aware of the impact of autogrowth settings (learned the hard way when restoring a small db that took ages to complete:)) but I thought I'd missed something in your wait stats list that would indicate high no. of VLFs being an issue.
Thanks for replying!