I'm studying SQL Server. I have read that a database can have several data files and one log file, but recently I was heard that a database can have more than one log file.
Is this true?
Asked
Active
Viewed 2,213 times
2 Answers
3
Having an additional log file doesn't actually hurt performance in and of itself. That doesn't however mean that you won't have performance issues when you have multiple files. If one of your files is on slower storage for example you could end up with intermittent performance issues which can be difficult to troubleshoot and cost you quite a bit of time. Also frequently when people use a second log file they end up with a larger log than they actually need. As pointed out by Paul Randal in the link in the comments this can cause you significant issues during recovery.
The important thing to remember is that the system writes to one specific point in the log, and writes sequentially. So if you have multiple log files you are only writing to one file at a time. This means that having more than one log file is generally pretty useless.
The only reason I've found that you actually want to have a second log file is to temporarily extend your log onto a second drive. You've run out of space for your existing log file and don't have time to add space to that drive. You then create a second log file on a different drive with more space. This should be a temporary situation however. You'll want to expand your existing space (or create a new one) and move your log back into a single file.
I wrote about it in more detail here if you are interested.
Kenneth Fisher
- 24,307
- 13
- 63
- 116
0
Its possible to add more than one log file but it doesn't do any good. In fact, it generally hurts performance and just adds overhead (plus anyone that does so ends up looking like they have no idea about SQL Server).
Rich
- 21
- 4