This blog post is mainly to address concerns about increasing log file space, and how to go about to shrink the file. I am adding a priliminary content here as of now, but I will update this content as and when I get time and new content to add.
Question: What is a log file, and why is it required?
Answer: A log file is a separate file, that stores the various transactions hitting the database. Each database has its own log file (one or more) and this (these) log file(s) are required to keep a history of the transactions that hit the database, so that the database can be recovered to a state of both physical and logical consistency in case of failure.
When a transaction is fired against the database, the transaction is first recorded in the log file, and then actual data pages, that need to be modified, are brought into memory. Later, these data pages in memory are changed. At certain intervals of time, a background process, known as a CHECKPOINT runs, which flushes the dirty pages (modified pages in memory) to the disk.
Question: How is a log file accessed?
Answer: A log file is always accessed sequentially. So as to be able to properly utilize log space, a log file is divided into logical segments called VLFs or Virtual Log Files. Each log file is divided into multiple VLFs. Now, when there is a new database, the log file, for all practical purposes is empty, and so are all VLFs. Writing starts from the first VLF, and when this VLF is full, writing continues to the second, and so on and so forth until the last VLF is reached. Now, when the last VLF is full, before growing the log file, the log writer determines if any of the preceding VLFs have been truncated (marked as inactive). In case it finds any such VLF, writing starts in the first inactive VLF in the chain.
For example, if a log file has 10 VLFs, then after the 10th VLF is full, the log writer will search for any inactive VLF in the chain. Let us assume that it finds VLF 3, 5, 6, 7 as inactive, then it will start writing into VLF 3.
In case no inactive VLF is found, the log writer will trigger a log file growth, thereby creating more VLFs at the expense of disk space.
Question: VLF truncated?
Answer: Most people assume truncation to be analogous to shrinking the file, however, in SQL Server terminology, truncation and shrinking is not the same. Truncation merely means marking a VLF as inactive, where as shrinking log file means to reduce the number of VLFs in the log file so as to reclaim disk space. There are different conditions when a VLF is truncated.
If a database is in simple recovery model, a VLF will be truncated only when all the conditions below are fulfilled:
All transactions that have begun in the specific VLF has been either committed or rolled back.
All transactions, that have at least one command written into the specified VLF have either been committed or rolled back. Assuming that a transaction has started in VLF 5 and has continued to VLF 7 through VLF 6, it has to be either committed or rolled back before VLF 6 can be truncated.
A checkpoint has run on the database.
All transactions, that have at least one command written into the specified VLF have been replicated.
If a database is in bulk-logged recovery model or full recovery model, a VLF will be truncated only when all the conditions below are fulfilled:
All transactions that have begun in the specific VLF has been either committed or rolled back.
All transactions, that have at least one command written into the specified VLF have either been committed or rolled back. Assuming that a transaction has started in VLF 5 and has continued to VLF 7 through VLF 6, it has to be either committed or rolled back before VLF 6 can be truncated.
A checkpoint has run on the database.
The VLF in question has been backed up by taking a log backup after the checkpoint has run.
All transactions, that have at least one command written into the specified VLF have been replicated.
Question: How does shrinking of a log file happen?
Answer: A log file is shrunk when you issue the following command to the SQL Server:
DBCC SHRINKFILE ('logical file name', targetsize)
When this command is issued, the log file is shrunk from the end till the last used VLF. Hence, if the last VLF is in use, the log file cannot be shrunk.
Question: How do I know if a log file can be shrunk?
Answer: To understand whether the log file can be shrunk, you will need to fire the following commands and understand their outputs. The first command that needs to be fired is:
DBCC SQLPERF(logspace)
This will let us know what percentage of the log file is actually in use. The lower the percentage, the more the file can be shrunk.
Now, run the following command:
DBCC LOGINFO(DatabaseName)
The output of this command will give you the status of each VLF in the log file. A status of 0 means that the VLF has already been truncated and can be overwritten. A status of 2 means that the VLF has active transactions and cannot be overwritten. Look for the last VLF, if the last VLF is 2, then file cannot be shrunk just yet. However, if it is 0, then that means that the file can be shrunk till the last VLF with a status of 2.
Question: Ok, so how do I proceed in case the last VLF has a status of 2?
Answer: First you will need to check the recovery model of the database. The general tab in the database properties dialog box will show the recovery model of the database. If the database is in simple recovery model, then open a new query window, change the database under context to the user database whose log needs to be truncated, and run the command: CHECKPOINT. See if that helps in truncating the last VLF; if not, you can consider trying again after an interval of time. Also, if the database is involved in transactional replication, run the LogReader Agent and keep it running for some amount of time, so that the commands can be replicated.
However, if the database is in bulked-logged or full recovery model, apart from running the CHECKPOINT command, take a log backup. See if that helps in truncating the last VLF, if not, probably you may have to wait for some time before trying again. Also, if the database is involved in transactional replication, run the LogReader Agent and keep it running for some amount of time, so that the commands can be replicated.
-------------------------------------
use master
go
--- first take a FULL backup
backup database db_name to disk = 'D:\backups\db_name_FULL.bak' with stats =10, compression, init
go
use db_name
go
---- manually issue a checkpoint
checkpoint
go
checkpoint -- run twice so the log file wraps around
go
----- now shrink the log file to reclaim disk space
dbcc shrinkfile(db_name_LOG,xxMB) -- xxMB is the size that you want.
go
