0

we have come across a strange phenomenon using a second transaction log file for a database. Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) Standard Edition. On Windows Server 2016, a single instance, no cluster or replication.

We have added the second log file after the first was already in use the whole time. We thought the second file would only be in use after the first one has no more space to grow / is full.

I know that it is probably not such a good idea to have two log files in the first place, but for the moment we would like to have the second one as an insurance, so to speak.

Currently there is enough free space on the first log volume, 416 GB, there are 983 GB free on the second. The first log file is allowed to have a maximum size of 300 GB, currently has a size of 40 GB, growth by 1024 MB, limit 300 GB. The second log is currently 16 GB, growth by 1024 MB, limit 2097152 MB.

Now when we execute

select * FROM sys.dm_db_log_info(DB_ID('MultileLogFiles'))

we see that both log files are actually in use (please see below) - or at least that is our conclusion, we may be wrong. The Resource Monitor supports our claim and shows that the second log file is also used, both appear from time to time and the 2nd one has also grown, not only the first one.

Could anyone give us a hint why that should be the case, why SQL Server does not wait until log file 1, the first in the list, is full or may be the other one and not the first, but why both? That seems strange.

Best regards,

Harry

database_id    file_id    vlf_begin_offset    vlf_size_mb    vlf_sequence_number    vlf_active    vlf_status    vlf_parity    vlf_first_lsn    vlf_create_lsn
    7            2             8192              0.24              603616                1            2            128    000935e0:00000010:0001    00000000:00000000:0000
    7            2             262144            0.24              603617                1            2            128    000935e1:00000010:0001    00000000:00000000:0000
    7            2             516096            0.24              603598                1            2            128    000935ce:00000010:0001    00000000:00000000:0000
(...)
    7            3             4437573632        128               603683                1            2            128    00093623:00000010:0001    00092757:0003fd82:002e
    7            3             4571791360        128               603684                1            2            128    00093624:00000010:0001    00092757:0003fd82:002e
(...) 
    7            3             15040774144       1024              603464                0            0            64     00000000:00000000:0000    00092787:001ffd67:00c0
    7            3             16114515968       1024              603466                0            0            64     00000000:00000000:0000    00092789:001ffd45:002e
(...)
2
1

The Log files are used like if it was only one file. Both files have VLF so let's say the first log file have VLF1 to VLF10 and the second one have VLF11 to VLF15. It will write down stuff in the VLF1 and once that one is full, will continue in VLF2, etc... once it reach VLF15, it will then go back to VLF1 (not before) so it's 100% normal that both files still get used.

If you want to get rid of your second file, you'll have to take log backup until the "active" VLF is on the file #1 and then, you'll be able to delete the file.

4
  • Thank you for your answer. I was surprised because my impression was that VLF1 to VLF10 (in your example) were not full and yet VLF11 and so on came into the game. Other than that it's clear. – Harry Liepelt Oct 21 '20 at 14:03
  • Actually, full is missleading... active should be the right term to use. If VLF1 was filled up and it now writing in VLF2. If there is no transaction or anything else olding VLF1, it will be marked as reusable by the next backup of the log. It will be "empty" but it will not be reused until you reach VLF15 – Dominique Boucher Oct 21 '20 at 14:06
  • Ok, thanks, how about autogrowth, log one (with VLF 1 to 10) has currently in theory 416 GB free space on the volume, autogrowth is set to 1024 MB, it is allowed to grow up to 300 GB and is currently at 40 GB size. Shouldn't it grow until 300 GB as set and then use VLF11 to VLF15 so to speak? – Harry Liepelt Oct 21 '20 at 14:13
  • Actually, autogrowth will be triggered when the active VLF is full and the next one is not mark as "reusable". So if the log from the file 1 are all being used, then VLF #11 will get used (file #1 will not growth). If VLF#1 to VLF#15 are all used and full, then an auto-growth will occur. – Dominique Boucher Oct 21 '20 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.