February 11, 2019 at 9:10 am
I think I may have the answer, I just cannot find the definitive version.
I have a database with 2 Transaction Logs and they insist on growing equally and side by side, even though I know the transaction log is written to sequentially (I read Paul Randall's posts and a few others). So here is what I think I know. I think the problem is caused by poorly sized log file(s). I read a bit about the VLFs and how they impact using 2 log files. I think the VLFs in one log file are used up and then the second log file is written to before either log file is grown. Once all VLFs are all full both log files will be grown.
For info, both log files are on independent drives.
I'm thinking of sizing file 1 properly, wait until the active VLF is being written to on File 1 and then looking to remove the second log file.
Any suggestions from the more knowledgeable people would be appreciated.
February 11, 2019 at 12:13 pm
Sequelsurfer - Monday, February 11, 2019 9:10 AMI think I may have the answer, I just cannot find the definitive version.
I have a database with 2 Transaction Logs and they insist on growing equally and side by side, even though I know the transaction log is written to sequentially (I read Paul Randall's posts and a few others). So here is what I think I know. I think the problem is caused by poorly sized log file(s). I read a bit about the VLFs and how they impact using 2 log files. I think the VLFs in one log file are used up and then the second log file is written to before either log file is grown. Once all VLFs are all full both log files will be grown.For info, both log files are on independent drives.
I'm thinking of sizing file 1 properly, wait until the active VLF is being written to on File 1 and then looking to remove the second log file.Any suggestions from the more knowledgeable people would be appreciated.
Are you regularly backing up the log for the database?
You can see the active portion of the log files by executing DBCC LOGINFO or selecting from sys.dm_db_log_info (for SQL Server 2016 SP2 and higher).
Active portion of the log has status of 2 and there will be two different file ids for the two log files. You should be able to tell if your theory is correct with those queries.
Sue
February 11, 2019 at 2:52 pm
Are the files growing?
February 13, 2019 at 8:39 am
Sue_H - Monday, February 11, 2019 12:13 PMAre you regularly backing up the log for the database?
You can see the active portion of the log files by executing DBCC LOGINFO or selecting from sys.dm_db_log_info (for SQL Server 2016 SP2 and higher).
Active portion of the log has status of 2 and there will be two different file ids for the two log files. You should be able to tell if your theory is correct with those queries.Sue
Sue, no we are not backing up the logs, application requirement I'm afraid. Understand the part about the active part of the log, that is helping with my digging.
February 13, 2019 at 8:50 am
Steve Jones - SSC Editor - Monday, February 11, 2019 2:52 PMAre the files growing?
Steve, yes they are. The bit that confused me was that they have both been growing simultaneously. I now believe that to be caused by both log files being shrunk on a regular basis. Neither file will grow independently until they have both filled up (that is my assumption, I am trying to prove it). I have grown one log file large enough to prevent further growth and shrunk the second log file. I don't believe the second file will ever get used again. (The shrinking is a Legacy operation that I have only just managed to convince them to stop doing, I have explained the issues around shrinking and am finally getting the team in question to understand. They have had serious log growth issues in the past that I am just getting rid of with the help of an upgrade to 2016 SP1 and table partitioning in Standard Edition).
February 13, 2019 at 12:07 pm
That should help. the log files are supposed to fill before moving to the next one, and it's possible that if they are closely sized and full that you're growing one, filling, then starting the second, filling growing it, repeat.
February 14, 2019 at 1:21 am
Steve Jones - SSC Editor - Wednesday, February 13, 2019 12:07 PMThat should help. the log files are supposed to fill before moving to the next one, and it's possible that if they are closely sized and full that you're growing one, filling, then starting the second, filling growing it, repeat.
Thanks Steve
February 14, 2019 at 8:36 am
Sequelsurfer - Wednesday, February 13, 2019 8:39 AMSue_H - Monday, February 11, 2019 12:13 PMAre you regularly backing up the log for the database?
You can see the active portion of the log files by executing DBCC LOGINFO or selecting from sys.dm_db_log_info (for SQL Server 2016 SP2 and higher).
Active portion of the log has status of 2 and there will be two different file ids for the two log files. You should be able to tell if your theory is correct with those queries.Sue
Sue, no we are not backing up the logs, application requirement I'm afraid. Understand the part about the active part of the log, that is helping with my digging.
Interesting that the application requires you do not back up the logs. I'm guessing that the application "requires" that the database be in simple recovery mode, that's the first time I ever heard of that. I know if you don't need to restore right up to the very last transaction, simple mode works, but what sort of requirement makes this mandatory? Just curious, if its company confidential, I understand!
February 17, 2019 at 11:51 pm
Why do you have 2 log files in the first place.
February 18, 2019 at 11:03 am
Legacy thing. They blew a 500gb log drive, and the only solution was to add another drive and log file. Im now getting rid of the second log file.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply