Recently I've discovered our 2012 SQL Server database contains over 100,000 Virtual Log files by running the DBCC LOGINFO command. After doing a good deal of research I am still uneasy as to what exact size I should set the Log after shrinking.
With using the code snippet below and based on my details, what suggested size should I set my Log file to and would you recommend that I change my autogrowth?
USE AdventureWorks GO BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak' GO -- Get Logical file name of the log file sp_helpfile GO DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY) GO ALTER DATABASE AdventureWorks MODIFY FILE (NAME = AdventureWorks_Log,SIZE = 1GB) GO DBCC LOGINFO GO
- Current Database Size: 39,482,368 KB
- Initial Database Size: 38,557,000 KB
AutoGrowth: 128 MB
Current Log File Size: 34,964,480 KB
- Initial Log File Size: 34,145,000 KB
- AutoGrowth: 128 MB
A Log backup is taken 8 times a day 6 days a week and Autogrowth is happening pretty much once a week. The Log file is also on a separate storage device from the Database
Also: Many of the VLF's are probably left over from the last DBA not shrinking the file. It has been within a year or two since we updated the autogrowth to 128 MB from the default and set this initial size.