Database log files (Shrink management)

SQL Server database log files are actually composed of a number of logical log files, we can use the command to look at the database log file

What you can see is that SQL Server database log files are composed of many files, when the database log file is full, auto-growth will generate new files, these files are called VLF (virtual log file), so at this level, we can simply interpret the database log as a train , a section of the compartment is understood as the database of virtual log files, of course, if not enough to save the next transaction log, it is necessary to expand, is to add a fire compartment.

So the database transaction log is actually composed of a number of independent virtual transaction log files, we look at, probably know that the database of virtual log file has its own state, also has its own LSN identity. How do you manage these database log files at the database level?

? Look at the effect of the LSN.

1: The serial number that LSN is used to identify VLF

2:LSN identifying the exact location of the log in the VLF file

Let's analyze the logical management of database logs?

We simply understand that the database log is in two states, the activity log and the inactive log (although the log has four states, but we understand that both States should be sufficient), the activity log header and tail of the tag is the head and tail log, the middle part is the database activity log, in fact, this diagram is not very intuitive, Because the database log management, more like a circular management, is able to achieve continuous management, when the database header log reaches the end of the data, if the first log file is available, the transaction log will not automatically grow, but to occupy the first database of VLF, which is very important, we can look at the The head log of the database is constantly moving forward while the tail log can change location.

If you want to change the location of the tail log, only the activity log decreases, and the activity log decreases, which means that the database log is truncated, so the tail log goes backwards every time the database transaction log truncation occurs.

The database log file has a status, status, you can observe the observation will be found, in fact, there are 0, 22 states, 0 is representative of idle, 2 for use.

Never underestimate this state, which is important for shrinking a database log file.

The reason that the log file can be shrunk is that the data at the end of the file is cleared so that the part of the space is freed, rather than escaping the tail to delete the contents of the header or the middle part of the file.

The shrinking of the SQL Server database is compressed from the last database log file, so the DBCC shrinkfile only works if the last VLF state is 0, otherwise shrinking the database log file is not effective.

Database log files (Shrink management)