Split log file or not?
-
I tried to do a search, but every time I click for the next set of matches it sends me back to the search page. Possibly this is a FireFox issue.
Anyway, someone here at work has told me that he heard its better not to split a transaction log. This file will have 40GB allocated to it. Are there any particular performance or reliability issues for whether or not to split this file? Disregard the filesystem because I'm stuck using a single RAID5 for now and there's nothing I can do about it.
-
Unfortunately, your browser issues is not firefox specific. There are some thing here that do not work like someone would expect them to.
But to answer to question: No, there is no particular advantage to have multiple log files. SQL Server will treat them internally as one single file. You can't decide what to log in what file.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I guess there are some good points to split log file. First, it enhance the restore operation when restoring from multiple files on separate disks. Second, if you have a disk space issue then splitting the log files across multiple disk will solve this problem.
-
Hm, might be my language barrier: Is the question about split the log file or strip the log file?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'm not sure what you mean by "strip", but I'm refering to either adding another physical file to the log or increasing the size of the existing file. I.E. whether to have 2 20GB files or 1 40GB file.
-
Your log file cound grow for many reasons. But you also can shrink it to a reasonable size bacause your database may not need such large log file. Run "dbcc sqlperf(logspace)" to check how much spaces bing used in the lig file by the database and go from there.
-
I'm refering to this what you can find in BOL under striping. I guess that's what you call split.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Its not a matter of why its growing. We set our log files to a certain restricted size. This post is about whether to double the log file size or add another log file of similarly restricted size. We restrict them to keep them from running out of disk and interfering with other databases.
-
... another log file of similarly restricted size
See my reply above.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Right, you said "strip" instead of "stripe" and that confused me a bit ;-), but this is indeed what I'm refering to.
-
Hey, as a non-native speaker, I have the advantage to be able to blame it on the language
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
My point is you may not even need a 40GB log file for the database. Set it auto grow with maxsize will prevent it to use all disk spaces. But you still have to fact that it has to grow if database transactions need.
As Frank said, there is no particular advantage to have multiple log files.
-
There's lots of reasons why I need to allocate this space. The main one is that tempdb resides on this volume as well and if it runs out of disk space that could be quite bad. I can't pre-allocate a large size for tempdb because we're creating CD image sets to manufacture these servers and I don't want to end up with a 20-CD image set. If the log file backups fail, its possible our customers could either ignore or not receive the warnings and if this happens, the log files fill up really fast. We've had cases where they went months without checking backups and we'd like to idiot-proof these boxes as well as possible. Having the server just quit accepting data if the log fills up (they would notice this immediately) sounds like a better solution than having the server crash because of lack of disk space. There are other things running on this partition as well that need some dedicated space and the log file for this database is less important than those. But, I'm not really wanting to change the course of the thread to discuss those because its a real pain to get changes through around here so I want to keep it simple and just decide whether to stripe the log file or not since that doesn't affect anything else on the server that I have no control over. Going by the posts so far, I guess I'll propose that we just increase the size of the log file. Thanks very much for your suggestions!
-
I've done a bit more research into how log files work and here's what I've found. The phyical logs are divided into many virtual logs that are created as your file grows. Transactions are inserted sequentially into these virtual logs and physical logs until it reaches the end, at which point it wraps back around to the beginning. Data is also cleared sequentially from the point of start transaction to end transaction, thus the log file continuously loops around itself. Given this information, it would seem that splitting a log file would indeed not have much (if any) performance advantage. The only performance suggestion I was able to find was to initally allocate the full size or grow by large increments to avoid small virtual files.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply