One.
That’s it, no more, just one.
Why is that? Because activity to the transaction log is written sequentially. Adding more logs does not help with performance. If you don’t believe me then perhaps you would believe Paul Randal? If you don’t believe him then you need more help that any one blog post could ever give.
Now, are you certain you only have one transaction log file per database? Are you really sure? Well, you can run this bit of code to find out for yourself:
SELECT db_name(database_id), COUNT(*) FROM sys.master_files WHERE type = 1 GROUP BY database_id HAVING COUNT(*) > 1
If you get no results, you’re good. If you do get results then you should start asking around as to why there is a database with more than one log file.
You should edit to link to his rant for the words “you need more help.”
KBK,
Too funny, I did think about doing that!
I’ve seen this many times, and I believe that most DBA’s do this with the illusion that it works on the same like adding multiple data files for TempDB.
Hey, nice point to share.
I just read an article about Log file and SQL(http://www.todo-backup.com/backup-resource/sql-backup-software/three-sql-server-recovery-models.htm
). Might be a good theory support of your idea.
Hi all,
I’m a DBA that specialises in both Oracle & SQL Server. You might be aware that Oracle allows you to multiplex your “online redo log files” (the equivalent of the transaction log). The Oracle database will therefore maintain all the copies of the redo log, will write to one if the other is unavailable & will improve recoverability if a disk fails. I suppose the bottom line is that it gives us DBAs another option – more flexibilty which is key when faced with disaster scenarios.
Any comments?
I’ve asked Microsoft if there were any plans to implement multiplexing bit no plans yet.
Thanks,
Chris.
Chris,
That does sound like a nice feature, and I wouldn’t be surprised if it has been discussed at Microsoft previously. I can look to bring it up again.
Thanks for the comment.
Tom
What is the downside to having multiple log files? I’m in a situation where disk space is tight. For a very large database, I need a log file that is larger than the available space on any of the disks on the server. Management has rejected my suggestion to add a disk. So my only solution was to split the log into two files. Paul talked about “making management more tricky” but failed to elaborate. In what way is management trickier?
Bob,
Not sure I understand how splitting your log into two files helped you, since you didn’t have enough disk space. My guess is that, as an example, you needed 10GB and couldn’t get that on one disk, so you created one log file of 5GB on one disk and another 5GB log file on the second disk. Does that sound right?
Your log file is circular and written to sequentially. Your situation seems to be an interesting one, no question. I don’t want to put words (or anything else) into Paul’s mouth, but from my perspective I would find it trickier for a handful of reasons. First, I have logs in two different locations, making it slightly harder to know where all my database files are. Second, restores have an extra “gotcha” that could go wrong. Third, I wouldn’t know which log files was being used without going to look at it first, which is also extra overhead I wouldn’t have if I used just the one file.
I’m sure I can think of more, but those came to mind first. Thanks for your comment, I will see if Paul can leave a comment as well and let us know about other pitfalls for having more than one log file.
Tom
Thomas, your guess is spot on. It was the only way I could get a log that was properly sized.
My test restore went without a hitch so I’m pretty confident about that.
not a bad script you have given in your site but it should be not enough plz give some more ok.