- log file always use sequentially , therefore put the multiple log file in the different disk drive would not improve performance or WRITE_LOG wait issue.
- log file must be zero out first and can not use instant initial file .Therefore shrink log file too often is not going to be good because the log file will grow again.
- Backup log would ONLY release the free VLF
How big the log file should be
As big as it needs to be. Here are some guild line:
- Single largest transaction
- Largest index or index rebuild
Due to the number of VLF could affect the performance, we should set up the initial log size as reasonable size and set the extend size in the fix number so each VLF would be in the difference size.
For example, set the initial size as 4GB ( 256MB per VLF) and extend another 4GB after that .
- chunks less than 64MB and up to 64MB = 4 VLFs
- chunks larger than 64MB and up to 1GB = 8 VLFs
- chunks larger than 1GB = 16 VLFs
Check log space usage
To see the log file space, we can use DBCC SQLPERF. example as below
Or you can use below query to check the space usage for single database
Virtual Log files
Unlink the data file, Having multiple data files on the different disk may increase the performance. SQL Server treat multiple log file as a whole. Regardless number of log file you have for a single database, the SQL server internally divide into the multiple virtual log files. We can not control the number of Virtual log files. DBCC LOGINFO can show the VLF detail.
Status 2: Contain the active portion of the log
Status 0: does not contain the active portion of the log.
When We use the BACKUP LOG to backup the log, the default is always truncate the log. ( Backup database will also backup the backs up enough of the transaction log to produce a consistent database when the backup is restored.) This will result of the log being truncate. After the log being truncate, we can use the DBCC LOGINFO to exam the VLF.
As you can see, the stratus column changes from 2 to 0. From active become inactive. The log files size does not change as we only truncate the log not change the file size. To change the files size, we need to shrink file. Shrink file ONLY works when there is inactive VLF.
Truncate only option will only remove inactive portion of the VLF in the end last active VLF. Therefore, it is expected only 6,7,8 will be removed.
A log file can only be shrunk to a VLF boundary, shrinking a log file to a size smaller than the size of a VLF might not be possible, even if it is not being used. The size of the VLF is chosen dynamically by the SQL Server when the log file created or extend.
DBCC opentran show the open transactions that are using the log space.
- Shrinking the Transaction Log http://msdn.microsoft.com/en-us/library/ms178037.aspx
- Understanding Logging and Recovery in SQL Server http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
- 8-Steps-to-better-Transaction-Log-throughout http://sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx