Sql-server – SQL Server second transaction log seems to be used as well – not only after first one is full

sql serversql-server-2016transaction-log

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
(...)

Best Answer

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.

Related Question