The transaction log for any database is managed as a set of virtual log files (VLFs). SQL Server determines VLF file sizes internally based on the total size of the log file and the growth increment that is used when the log expands. A log always expands in units of whole VLFs and it can only compress to a VLF boundary. A VLF can exist in one of three states: ACTIVE, RECOVERABLE, and REUSABLE.
- ACTIVE: The active part of the log begins at the minimum log sequence number (LSN) that represents an active (uncommitted) transaction. The active part of the log ends at the last-written LSN. Any VLFs that contain any part of the active log are considered active VLFs. (Unused space in the physical log is not part of any VLF.)
- RECOVERABLE: The part of the log that comes before the oldest active transaction is only necessary to maintain a sequence of log backups for recovery.
- REUSABLE: If you are not maintaining transaction log backups, or if you already backed up the log, SQL Server reuses VLFs before the oldest active transaction.
When SQL Server reaches the end of the physical log file, it starts reusing that space in the physical file by issuing a CIRCLING BACK operation to the beginning of the files. In effect, SQL Server recycles the space in the log file that is no longer necessary for recovery or backup purposes. If a log backup sequence is being maintained, the part of the log before the minimum LSN can’t be overwritten until you back up or truncate those log records. After you perform the log backup, SQL Server can circle back to the beginning of the file. After SQL Server circles back to start to write log records earlier in the log file, the reusable part of the log is then between the end of the logical log and active part of the log.
Truncation of the transaction log file differs from the compression of the transaction log file. When SQL Server truncates a transaction log file, this means that the contents of that file (for example, the committed transactions) are deleted. However, when you are viewing the size of the file from a disk space perspective (for example, in Windows Explorer or by using the dir command), the size remains unchanged. However, the space inside the .ldf file can now be reused by new transactions. Only when SQL Server shrinks the size of the transaction log file do you actually see a change in the physical size of the log file.
- For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.
- You can use alerts or monitoring programs to proactively monitor file sizes. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.
- Your autoshrink and auto grow settings must be carefully evaluated and they must not be left unmanaged.
- Your autogrow increment must be large enough to avoid the performance penalties. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. To reduce the impact on the virtual log file sizes, the best target growth size is 8 GB as the initial size and 8 GB auto growth increment.
- Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.
- Keep the size of your transactions as small as possible to prevent unplanned file growth.