This happens when you perform backup of the transaction log, i.e., the BACKUP LOG command will truncate the log after producing the backup.
In full recovery, it is your responsibility to truncate the log. You can even stress this using the CHECKPOINT command. The log is typically truncated when a checkpoint occurs (reference the first paper), which is done automatically now and then. Since SQL Server will truncate the log for you, you cannot perform backup of the transaction log – the BACKUP LOG command will return an error if you try. However, you can still end up with large ldf files due to long-running transactions and problems with the log reader when using transactional replication. In simple recovery, it is not your responsibility to “empty the log” (or “truncate the log”), as SQL Server will do that for you. This recovery model is designed to be used when you do not perform backup of the transaction log of the database. The default value – what you get when you create a database – is inherited from the model database, and by default is in full recovery. Most installations and databases are either in simple or full recovery model. The available modes are full, simple and bulk logged. The recovery model settingĪ database option called the recovery model, is all about management of the transaction log. We sometimes refer to this as “truncate the log,” or as I prefer to say “empty the log.” However, technically, we make SQL Server mark as many VLFs as possible as OK to use – as free, reusable, or “OK to overwrite”. What you need to do is make VLFs reusable. If all VLFs in the ldf file are in use, then the ldf file has to grow – or if it cannot grow, then the modification will return an error message and fail. When the head reaches the end of the current VLF, SQL Server has to find a VLF that it can use.
Also, imagine SQL Server having a series of log records with a head and a tail.
A VLF can be in use or it can be free for SQL Server to use (slightly simplified, but enough for our purposes). So, think of the ldf file internally as a series of VLFs.
Search the Internet for terms such as “VLF” and “shrink” and you will find details on how to determine if you have many VLFs and how to properly manage them. Things such as startup and restore of the database can be slower with many VLFs. There are some disadvantages of having “too many” VLFs, such as the case when the ldf file has grown frequently. This is performed automatically by SQL Server, and a DBA typically does not have to be aware of VLFs. The transaction log file (or files) is internally divided into Virtual Log Files(VLFs). Ultimately, it is the DBA’s responsibility to make sure the transaction log doesn’t fill up the disk, as log records are generated for our modifications. Please reference the first paper in this series for a more detailed discussion about storage architecture and transaction logging. The transaction log lives in the ldf file(s) of the database. Every modification is logged in the transaction log before the modification is performed on the actual data page. Transaction logging and management of the transaction log For that, please refer separately to the Veeam and SQL Server documentation that accompanies each product. This is not intended to be a reference paper covering all the options in the graphical user interfaces (GUIs) or the SQL commands. The paper is both targeted to the SQL Server DBA, as well as the backup operator who may have more experience with Veeam and less with SQL Server. The purpose of this paper is to describe SQL Server backup in general and also the options you have for backing up your Microsoft SQL Server databases in conjunction with Veeam®. Find a Veeam Accredited Service Partner.Alliance Partner Integrations & Qualifications.Veeam Backup & Replication Community Edition.