Wednesday, January 5, 2011

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.

No comments:

Post a Comment