Exploring SQL Server Virtual log files

SQL Server divides transaction Log files into multiple VLFVirtual Log files), that is, virtual Log files.

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/230034D28-0.png "alt =" 112954536.png"/>

1. Four statuses of VLF

1,Active. Indicates that an active transaction exists in VLF, that is, an unfinished transaction ).

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 recovery mode is set to simple. The initial status of mdf and ldf files is as follows:

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/23003453W-1.png "alt =" 103942391.png"/>

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 ".

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/230034MX-2.png "alt =" 114633236.png"/>

4. Check VLF

Run dbcc loginfo to check VLF usage.

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/2300344L2-3.png "alt =" 104823245.png"/>

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 name = 'db01'

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/2300345b0-4.png "alt =" 132718935.png"/>

5. View LSN in VLF

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

Select * from: fn_dblog (1865000000000000001,186 60010000000000000)

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/2300346007-5.png "alt =" 143215130.png"/>

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" is still not written back to the mdf file in the data buffer, You can manually execute the checkpoint so that "dirty data" can be written back immediately. In the previous example, we can see from the sys. databases system view that a transaction has not been completed, resulting in checkpoint latency)

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.

(When to execute checkpoint, please refer to the SQL Server when the "dirty page" back to the hard disk "http://jimshu.blog.51cto.com/3171847/1202748)

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

Check "disk usage ".

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/2300341958-6.png "alt =" 105135233.png"/>

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 of Status = 0 (reusable and unused), and then compresses the LDF border.

Iii. VLF in full recovery mode

In full recovery mode, performing full backup on the database does not affect VLF. Only after the transaction log is backed up, VLF will mark the recoverable VLF as a reusable state. If there are operations such as log transfer and image, some operations will be restricted ).

If VLF cannot be reused, LDF files will 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)

Http://msdn.microsoft.com/zh-cn/library/ms190925.aspx#Truncation

2. Database checkpoint (SQL Server)

Http://msdn.microsoft.com/zh-cn/library/ms189573.aspx

3. Introduction to the growth of Database Log Files

Http://blogs.msdn.com/ B /apgcdsd/archive/2011/12/30/10251946.aspx

This article is from the "MSSQL we have Chased Together" blog, please be sure to keep this source http://jimshu.blog.51cto.com/3171847/1174474