Database Reference
In-Depth Information
If you are using Simple or Bulk Logged Recovery Model, data loss is a
possibility. When using Simple Recovery Model for your database, it is not
possible to perform media recovery without data loss, and features such as
AlwaysOn Availability Groups, Database Mirroring, Log Shipping, and Point in
Time Restores are not available. For more information on recovery models, refer
When it comes to storage performance and sizing of your transaction log, the total size
and how fast you can write transactions to it are important but are not the only
considerations. You must also consider the performance of file growth, DB restart, and
backup and recovery operations. With this in mind, it is critical that not only is the total
size of your transaction log appropriate, but also how you grow your transaction log to
that size.
The reason this is so critical is that in SQL Server, even though your
transaction log may be one physical file, it's not one physical transaction log.
Your one physical transaction log is actually made up of a number of smaller units
called Virtual Log Files (VLFs). VLFs are written to sequentially, and when one VLF is
filled, SQL Server will begin writing to the next. They play a critical part in the
performance of database backup and recovery operations.
The number of VLFs is determined at the time a file is created or extended by the initial
size allocated to the transaction log and the growth amount “chunk” each time it is
increased in size. If you leave the default settings with a large database, you can quickly
find yourself with tens if not hundreds of thousands of VLFs, and this will cause a
negative performance impact. This is why the process of preallocating the transaction
log file and growing it by the right amount is so important.
Tip
To learn more about the physical architecture of SQL Server transaction log files,
If the VLFs are too small, your maintenance, reboots, and database recovery operations
will be excruciatingly slow. If your VLFs are too big, your log backups and clearing
inactive logs will be excruciatingly slow and may impact production performance. The
reason for the former is that SQL Server must load the list of VLFs into memory and
determine the state of each, either active or inactive, when doing a DB restart or
recovery. The latter is because a VLF can't be cleared until the SQL Server moves onto
the next one.
less at a time, you will get four VLFs each time. If you need 200GB of transaction log,
