April 26, 2005 at 4:33 am
Hi Gurus,
We can create multiple trnsaction log files (.ldf) for the database. Log files are written sequentially. But if we have say 10 .ldf files for a database, how does sql server writes to these log files. Are they written all together parallely or is there some other mechanism that sql server follows ?
Any ideas ?
--Kishore
April 26, 2005 at 4:50 am
If the log contains multiple physical log files, then the logical log will move through all of the physical log files before it wraps back to the start of the first physical log file.
SEE BOL
April 26, 2005 at 5:06 am
The log file(s) are always written sequentially. If there are more than one then only one will be used at a time. When it is filled the log will move on to the next file. As far as I know, there is actually no reason to have more than one log file. In fact it might lead to difficulties regarding managability, for instance when attaching databases.
April 26, 2005 at 5:33 am
We had a perf. problem several months ago and sent logs/data to MS. We asked about multi. T-log files and were told that there is a very specific scenario that they help in but majority of the time do nothing to help. We still implemented the multi. t-log files (despite being told that our case did not meeting the criteria). They did nothing to help with perf. We have since removed the secondary t-log file and perf. is still the same.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 26, 2005 at 5:43 am
Did they say anything more regarding the scenario in which multiple log files would be useful? It would be interesting to hear about it.
April 26, 2005 at 6:12 am
I believe the only reason for it's support is to be able to span multiple logical drives. That is the only reason I have ever seen where you would need to do this. But you would have to have an enormous amount of transactions to make it more worthwhile than buying a new machine.
April 26, 2005 at 6:43 am
Thanks for the replies. Highly appreciated.
--Kishore
April 26, 2005 at 4:14 pm
the specific scenario that MS referred to was contention in the LDF when a lot of work was being done in TEMPDB again SPECIFIC pattern ONLY. The creation of multiple t-log files for that scenario app. alleviated the contention to access a specific location for TEMPDB,,,,
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply