Exploring SQLServer virtual log files

SQLServer divides transaction log files into multiple VLF (VirtualLogFile), that is, virtual log files. 1. Four statuses of VLF: 1. active. Indicates an active transaction (unfinished transaction) in VLF ). 2. recov

SQLServer divides transaction log files into multiple VLF (VirtualLogFile), that is, virtual log files. 1. Four statuses of VLF: 1. active. Indicates an active transaction (unfinished transaction) in VLF ). 2. recov

SQL Server divides transaction Log files into multiple VLF (Virtual Log File), that is, Virtual Log files.

1. Four statuses of VLF

1. active. Indicates an active transaction (unfinished transaction) in VLF ).

2. recoverable. It indicates that all transactions in VLF have been completed, but some operations (such as database mirroring and replication) still need to use the data, so it cannot be overwritten.

3. reusable. It indicates that data in VLF is no longer needed and can be overwritten.

4. unused. Indicates that VLF is never used.

Ii. VLF in simple recovery mode

1. Initial File status

The test database name is db01 and the website space is set to simple in recovery mode. The initial status of mdf and ldf files is as follows:

2. Fill in Data

Run the following script to fill in a large amount of data:

Create table testtable (UserID varchar (100), pwd varchar (200 ))
Declare @ I int
Declare @ d1 datetime
Set @ I = 0
-- Truncate TABLE [testtable]
Insert into testtable select 'A', 'B'
While @ I <18
Begin
Set @ d1 = getdate ()
Insert into [testtable]
Select UserID + cast (floor (rand () * 1000) as nvarchar (50 )),
Cast (floor (rand () * 100000) as nvarchar (50) + UserID + cast (floor (rand () * 100) as nvarchar (50 ))
From [testtable]
Set @ I = @ I + 1
-- Print 'quarter '+ Cast (@ I as varchar (50) +' Circle, time: '+ Cast (datediff (MS, @ d1, getdate ()) as varchar (50) + 'ms'
End

3. Check disk Growth

After the script is run, the number of LDF files increases from 1 MB to 53 MB.

Choose Report> standard report> disk usage ".

4. Check VLF

Run dbcc loginfo to check VLF usage.

In the preceding table, FileID = 2 indicates the database's 2nd files. For the test database db01, the 2nd files are unique LDF files. FileSize indicates the VLF size. FSeqNo indicates the serial number of VLF. If it is zero, it indicates that this VLF is not used. If the CreateLSN is zero, the VLF is created when the database is created. If the CreateLSN is not zero, the VLF is created when the LSN is generated.

The Status column indicates the VLF Status. If it is 0, it indicates that the VLF is reusable or unused. If it is 2, it indicates that the VLF is active or recoverable.

You can view the cause that transaction logs cannot be truncated in sys. databases System View. For example:

Select log_reuse_wait, log_reuse_wait_desc from sys. databases where

5. View LSN in VLF

Through dbcc loginfo, you can find the minimum FSeqNo = 1865 of Status = 2 and the server space. Then, you can use the following statement to query the LSN in this VLF.

Select * from: fn_dblog (1865000000000000001,186 60010000000000000)

The hexadecimal LSN displayed in the 1st Column cannot be used directly. It must be converted to a normal hexadecimal LSN. In the first behavior example, the first segment is the VLF serial number, and the 749H is converted into a 10-digit System of 1st. The second segment is the 1865 slice Number of the log block, 6EH is converted into a 10-digit hexadecimal value of 0000000110, a 3rd segment is the serial number in the sector, and 1 H is converted into a 10-digit hexadecimal value of 4 characters. Therefore, a normal LSN is 186500000001100001.

6. checkpoint impact

From the table above, we can see that there are some VLF with Status = 2. If some "dirty data" in the data buffer is not written back to the mdf file, you can run the checkpoint command to immediately write the "dirty data" back. (In the previous example, we can see from the sys. databases system view that a transaction has not been completed, resulting in a checkpoint delay)

Note: In the production environment, even in the simple recovery mode, if a Transaction has been started in Transaction for a long time and has been forgotten by Commit/Rollback, A large log file may be displayed.

Run dbcc loginfo to check VLF usage. It can be seen that the vast majority of VLF has Status = 0.

Check "disk usage ".

7. Shrink log files

In simple recovery mode, automatic or manual checkpoint operations and full backup of the database mark the recoverable VLF Status as Status = 0, which can be reused continuously. LDF files do not need to grow if there are not a large number of transactions leading to checkpoint latency or "dirty data" Write-back latency.

When the log file is shrunk, the database engine checks the VLF Status, releases the space occupied by VLF with Status = 0 (reusable and unused), and then compresses the LDF border.

Iii. VLF in full recovery mode

In the full recovery mode, the transaction log needs to be backed up separately, so the checkpoint will be delayed, so that the recoverable VLF cannot be marked as reusable. Because these VLF cannot be reused, LDF files continue to grow.

After the transaction log is backed up, the VLF Status is marked as Status = 0 to be reused.

To avoid excessive LDF growth, increase the frequency of transaction log backup.

Iv. References

1. Transaction Log (SQL Server)

2. Database checkpoint (SQL Server)

3. Introduction to the growth of Database Log Files

This article is from the "JimShu (mcui/MCSE/MCT)" blog. Be sure to keep this source