SQL Server Database Log File Virtual Log File Sizing Considerations

SQL Server's database log growth algorithm automatically creates virtual log files (VLFs) with every log growth.  The algorithm tries it's best to carve out the virtual log files to the appropriate size.  However, left unattended log file growth can create an unnecessarily large number of virtual log files which as Linchi Shea has pointed out, can cause performance problems.  Out of curiosity, I created a couple of tables to see how SQL Server's log would grow if you set it to a fixed size with an equivalent auto-growth size.  So, if you haven't set your database logs minimum size and auto-growth size, you might want to check out how many VLFs you have out there by running DBCC LOGINFO in the database in question. The number of rows returned is how many VLFs your database has.  Keep in mind that the default auto-growth amount is 10% which makes the growth recursive and therefore, variable in size during each growth interval. NOTE: Before you run out and make your databases use very large auto-growth sizes consider this: Kimberly Tripp suggests 8 GB growth intervals for log files, but in my testing I've seen some (slow) servers take several minutes to zero initialize an 8 GB log file, so use with caution.   However, another server could perform that operation in 20 seconds.  Finally, be aware that there's a bug out there for growing in increments of 4 GB.

START/AUTO GROWTH  SIZE (MB)	NUMBER OF  VLFs	VLF SIZE (MB)	# VLFS  IN 5 GB LOG	# VLFS   IN 10 GB  LOG	# VLFS  IN 20 GB LOG	# VLFS  IN 30 GB LOG
2	4	1	10,240	20,480	40,960	61,440
4	4	1	5,120	10,240	20,480	30,720
8	4	2	2,560	5,120	10,240	15,360
16	4	4	1,280	2,560	5,120	7,680
32	4	8	640	1,280	2,560	3,840
64	4	16	320	640	1,280	1,920
128	8	16	320	640	1,280	1,920
256	8	32	160	320	640	960
512	8	64	80	160	320	480
768	8	96	53	107	213	320
1,024	8	128	40	80	160	240
1,536	16	96	53	107	213	320
2,048	16	128	40	80	160	240
3,072	16	192	27	53	107	160
4,096	16	256	20	40	80	120
5,120	16	320	16	32	64	96
6,144	16	384	N/A	27	53	80
7,168	16	448	N/A	23	46	69
8,192	16	512	N/A	20	40	60
9,216	16	576	N/A	18	36	53
10,240	16	640	N/A	16	32	48

To set your database auto-growth size, use this query as an example:



ALTER DATABASE [mydb] MODIFY FILE ( NAME = N'mydblog', SIZE = 4096MB )





Keep in mind that tempdb gets re-created during start-up so making this change will be temporary in TempDB until the next reboot. If you run something like DBCC SHRINKFILE( templog, TRUNCATEONLY ) you'll revert to whatever you had before you ran ALTER DATABASE.