1)What is Sql Server VLF ?
VLF is known as virtual log file. Sql server log file internally maintains multiple log file which are know as virtual log files. they use them internally as smaller files are easier to manage than the one big large file
2) Why VLFs are important ?
VLF's are important because they can really make or break the database performance. Too many VLF can cause internal defragmentation & poor performance. Too less VLFs can cause issues like high restoration times etc..
3)How VLFs are created ?
When ever log file gets filled up it grows by autoincrement (or) by fixed sizes as specified .
Number if VLFs created depends on the size of growth we specify
a) if the file growth is 64mb , then the new log file portion will contain 4 VLFs
b) if the file growth is between 64mb & less than 1 gb , then it will contain 8 VLFs
c) if the file growth is atleast 1gb or more ,then it will contain 16VLFs
4) What are the ideal number of VLFs per Database ?
There is not fixed count but any where around 50-100 VLFs is a good number for VLDBs ( Very Large Databases )
5) How to find the VLFs Count on a database ?
DBCC loginfo(databasename) gives the number of VLFs
6) How to Fix the too many VLFs ?
a) need to take a full back up & log back up
b) need to shrink the log file
DBCC SHRINKFILE (logfilename , 0, TRUNCATEONLY)
DBCC SHRINKFILE (logfilename , 0)
c) alter database log file initial size to big enough number & set the auto increment to grow by fixed MB ( reasonable big number)
ALTER DATABASE databasename MODIFY FILE (NAME = filename, SIZE = ---MB)
7) How to find the percentage of Log file used ?
DBCC SQLPERF (LOGSPACE);
On a final note : Kimberly has a posted a note on her blog that there is a bug in how the sql server calculates the size when the increments are in multiples of 4GB , so make sure that you don't set the values in multiples of 4GB.
VLF is known as virtual log file. Sql server log file internally maintains multiple log file which are know as virtual log files. they use them internally as smaller files are easier to manage than the one big large file
2) Why VLFs are important ?
VLF's are important because they can really make or break the database performance. Too many VLF can cause internal defragmentation & poor performance. Too less VLFs can cause issues like high restoration times etc..
3)How VLFs are created ?
When ever log file gets filled up it grows by autoincrement (or) by fixed sizes as specified .
Number if VLFs created depends on the size of growth we specify
a) if the file growth is 64mb , then the new log file portion will contain 4 VLFs
b) if the file growth is between 64mb & less than 1 gb , then it will contain 8 VLFs
c) if the file growth is atleast 1gb or more ,then it will contain 16VLFs
4) What are the ideal number of VLFs per Database ?
There is not fixed count but any where around 50-100 VLFs is a good number for VLDBs ( Very Large Databases )
5) How to find the VLFs Count on a database ?
DBCC loginfo(databasename) gives the number of VLFs
6) How to Fix the too many VLFs ?
a) need to take a full back up & log back up
b) need to shrink the log file
DBCC SHRINKFILE (logfilename , 0, TRUNCATEONLY)
DBCC SHRINKFILE (logfilename , 0)
c) alter database log file initial size to big enough number & set the auto increment to grow by fixed MB ( reasonable big number)
ALTER DATABASE databasename MODIFY FILE (NAME = filename, SIZE = ---MB)
7) How to find the percentage of Log file used ?
DBCC SQLPERF (LOGSPACE);
On a final note : Kimberly has a posted a note on her blog that there is a bug in how the sql server calculates the size when the increments are in multiples of 4GB , so make sure that you don't set the values in multiples of 4GB.
No comments:
Post a Comment