patternsqlMinor
curious case of two log files in sql database
Viewed 0 times
casecuriouslogsqldatabasetwofiles
Problem
I was doing a POC(proof of concept) for which I created two log files in a test database. I created a table and inserted 10000 rows. I observed that both my log files were getting used.
I know that logs are written serially, and in my case both log files have space on disk with autogrowth enabled, then why my second log file was getting used? Why not use a single log file and move on to second log file when space is not available. Isn't it supposed to be like this.?
I know that logs are written serially, and in my case both log files have space on disk with autogrowth enabled, then why my second log file was getting used? Why not use a single log file and move on to second log file when space is not available. Isn't it supposed to be like this.?
Solution
Standard warning about not using multiple log files etc etc.
You have two log files and are wondering why the first one didn't grow to fill all of the available disk space (or limit on the file size) before moving on to the second file. Correct?
A little bit of pre-information. Log files are broken up into multiple virtual log files (VLFs). If you search for VLF you can find a lot of information on them and when you have too many or too few, but that's a different subject.
If it helps you can think of the multiple log files as a single big circle. The log information will be written round and round in that circle. As each VLF is filled up the next is checked until it hits a VLF that is already in use. Only at that point will there be an autogrowth. I believe the currently used file will be the one autogrown. If it can't be (file size limit or out of disk space) then the next file is checked and so on. Only if none of the files can be grown will you get an out of space error.
You have two log files and are wondering why the first one didn't grow to fill all of the available disk space (or limit on the file size) before moving on to the second file. Correct?
A little bit of pre-information. Log files are broken up into multiple virtual log files (VLFs). If you search for VLF you can find a lot of information on them and when you have too many or too few, but that's a different subject.
If it helps you can think of the multiple log files as a single big circle. The log information will be written round and round in that circle. As each VLF is filled up the next is checked until it hits a VLF that is already in use. Only at that point will there be an autogrowth. I believe the currently used file will be the one autogrown. If it can't be (file size limit or out of disk space) then the next file is checked and so on. Only if none of the files can be grown will you get an out of space error.
Context
StackExchange Database Administrators Q#149397, answer score: 4
Revisions (0)
No revisions yet.