This has the effect that when the end of the transaction log is reached, SQL moves back to the start of the log (The first inactive VLF) and begins to recycle. There is a misconception that if the loop of VLFs catches up with itself, you will receive a 9002 (Log full) error. This is incorrect. SQL will actually grow the log, thus creating additional VLFs, and "skip" the active VLFs. When the loop next goes round, the "skipped" VLFs we be reused as normal (Providing it has now been marked as inactive). The only exception to this rule is quite an obvious one...If the maximum size you have set for the log file has been reached, or the drive is full. Obviously, if either of those conditions are met, you will receive a 9002 error as you would expect.
So, back to the point of this post...How many VLFs do you have? Well, this depends on the initial size of the log when you created it, how many times it has grown and by what size, also if you have shrunk the log file.
The math for the number of VLFs is as follows:
When the log is first created, if the log is less than 64MB, there will be 4 VLFs, if the log is larger than 64MB and smaller than 1GB then there will be 8 VLFs, and if the log is larger than 1GB, then there will be 16 VLFs.
Then, every time the log grows, the same rules will be applied. If you grow the log by less than 64MB, SQL will create an additional 4 VLFs, if you grow the log by between 64MB and 1GB, then SQL will add 8 new VLFs, and growing the log by more than 1GB, will result in 16 VLFs being added.
As a performance consideration, it is generally recommended that you have less than 50 VLFs and it is recommended that you grow your log file 8000MB chunks, in order to keep your VLFs at around 500MB in size. You should specifically not grow you log by exactly 8GB (or exactly 8192MBs), as this exposes a bug, that calculates the size incorrectly if it grows in multiples of 4GB, that makes it grow less than 1MB at a time, and kills your database!
So, the big question...Is it possible to actually see how many VLFs you currently have, and how many are active, etc? Well, of course it is! You can do exactly that by using DBCC LOGINFO.
Firstly, I created the database with the following script...
...If we then examine DBCC LOGINFO
RecoveryUnitId
|
FileId
|
FileSize
|
StartOffset
|
FSeqNo
|
Status
|
Parity
|
CreateLSN
|
0
|
2
|
134152192
|
8192
|
34
|
2
|
64
|
0
|
0
|
2
|
134152192
|
134160384
|
0
|
0
|
0
|
0
|
0
|
2
|
134152192
|
268312576
|
0
|
0
|
0
|
0
|
0
|
2
|
134152192
|
402464768
|
0
|
0
|
0
|
0
|
0
|
2
|
134152192
|
536616960
|
0
|
0
|
0
|
0
|
0
|
2
|
134152192
|
670769152
|
0
|
0
|
0
|
0
|
0
|
2
|
134152192
|
804921344
|
0
|
0
|
0
|
0
|
0
|
2
|
134668288
|
939073536
|
0
|
0
|
0
|
0
|
...As you can see, 8 VLFs have been created, as you would expect. In this output, FileID is the ID of the physical log file, FileSize is the size of the VLF, StartOffset marks the beginning of the VLF in relation to the beginning of the physical Log, FSeqNo is the sequence number of the VLF, Status is set to 0 for inactive and 2 for active, (so if you were troubleshooting a log that would not truncate, one point of call would be to look in this columns for lots of 2s!), Parity is a parity bit column, and CreateLSN is the first LSN of the VLF. You may know that a description of RecoveryUnitID is conspicuous by it's absence. This is because is has only appeared in Denali, and tonight is the first time I have seen it, so more details to follow, but I suspect it ties in with the new HADR features!?! Watch This Space for updates!
Anyway, I digress...If we now increase the log size by an additional 8GB, (This will not expose the bug for me, as I am running Denali, and the bug is fixed in this release. If I were running on SQL 05 or 08, I would have increased by 8000MB), it will add 16 new VLF, giving us a total of 24. This means that we see the following results...
RecoveryUnitId
|
FileId
|
FileSize
|
StartOffset
|
FSeqNo
|
Status
|
Parity
|
CreateLSN
|
0
|
2
|
134152192
|
8192
|
34
|
2
|
64
|
0.00
|
0
|
2
|
134152192
|
134160384
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
268312576
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
402464768
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
536616960
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
670769152
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
804921344
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134668288
|
939073536
|
0
|
0
|
0
|
0.00
|
0
|
2
|
536870912
|
1073741824
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
1610612736
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
2147483648
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
2684354560
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
3221225472
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
3758096384
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
4294967296
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
4831838208
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
5368709120
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
5905580032
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
6442450944
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
6979321856
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
7516192768
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
8053063680
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
8589934592
|
0
|
0
|
0
|
34000000006600000.00
|
0
|
2
|
536870912
|
9126805504
|
0
|
0
|
0
|
34000000006600000.00
|
...Finally, if we try to shrink the log file back down to 950MB, because we are just deleting inactive VLFs, we will end up with our original 4 VLFs, with the same original sizes, totaling 1GB, not 950MB...
RecoveryUnitId
|
FileId
|
FileSize
|
StartOffset
|
FSeqNo
|
Status
|
Parity
|
CreateLSN
|
0
|
2
|
134152192
|
8192
|
34
|
2
|
64
|
0.00
|
0
|
2
|
134152192
|
134160384
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
268312576
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
402464768
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
536616960
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
670769152
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134152192
|
804921344
|
0
|
0
|
0
|
0.00
|
0
|
2
|
134668288
|
939073536
|
0
|
0
|
0
|
0.00
|
...Hope you found this useful, and I will post an update, with the meaning of RecoveryUnitID column, as soon as I have it, unless of course you already know, in which case, please post a comment!
CREATE DATABASE LogInfoTest
ON PRIMARY
( NAME = N'instantinittest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\loginfotest.mdf')
LOG ON
( NAME = N'instantinittest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\loginfotest_log.ldf' , SIZE = 1GB)
Find my book, Pro SQL Server Administration on Amazon -
America
United Kingdom
No comments:
Post a Comment