June 16, 2008 at 4:34 am
I am trying to understand the fundamentals of having multiple transaction log files for a database. I want to know, when should we go for such a configuration and what are the pros/cons for the same. Any useful links elaborating the topic will also do good.
-Pritesh
MCITP(Administration)
June 16, 2008 at 4:58 am
AFAIK, It doesn't make sense to have multiple transaction logfiles (and this is what I noticed in a log transaction)
Unlike the multifile TempDB advantage, this seems not the case for multiple transaction logfiles. File 1 is filled and after that, file 2 will be filled.
Wilfred
The best things in life are the simple things
June 16, 2008 at 5:08 am
Log is written sequentially. Therefore, multiple files will not improve performance.
You can go for it to resolve free disk space issue.
June 16, 2008 at 5:13 am
I agree with you guys !
Thanks
😉
June 17, 2008 at 2:45 am
We've just had to implement the 2 transaction log files for dealing in space issues- works very smoothly. Although it won't improve performance
http://www.ITjobfeed.com
June 17, 2008 at 3:12 am
We've just had to implement the 2 transaction log files for dealing in space issues- works very smoothly. Although it won't improve performance
http://www.ITjobfeed.com
June 18, 2008 at 10:51 am
You may be adding more risk to the environment by having two log files. The reason would be evident upon either the loss or corruption of a single log file. Normally, when there is only one log file, if its missing, SQL will recreate it. If its corrupt, you can delete it, and then sql will recreate it. In the event of two log files, SQL will not recreate the files.
I suggest testing it out just to be sure.
June 18, 2008 at 12:29 pm
All the above answeres make sense of having one log file per database.
Transaction log file is optimised for sequential write, and hence get best performance if have only one trnasaction log file is in a seperate disk other than the data file and backup drive. in this case there will be only sequntial I/O in the disk rather random IO.
If you place multiple log files in the same disk, the disk may go for random IO which will hit the performance.
subban
July 1, 2008 at 11:00 pm
Whilst it does make sense to only have 1 transaction log per User DB, any idea about tempdb? The recommendation from M$ was to have 1 data file per CPU but they didnt mention anything about the number transaction log. I would assume 1 transaction log would suffice for multiple tempdb data files as well, but has anyone came across any credible evidence for this recommendation?
- Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008
July 2, 2008 at 4:55 am
I agree that 1 transaction log is the way to go - but there are occassions , in emergencies when space issues , allow some flexibility in defining more than 1 log file e.g across multiple drives
http://www.ITjobfeed.com
July 2, 2008 at 5:24 am
I agree that 1 transaction log is the way to go - but there are occassions , in emergencies when space issues , allow some flexibility in defining more than 1 log file e.g across multiple drives
http://www.ITjobfeed.com
March 26, 2009 at 2:21 pm
One should monitor log file regularly and should be taking regular transaction log backups.
Now, if taking log backups isn't needed, then you can do a "dbcc Shrinkfile" on the logfile when the "Log_reuse_wait_desc" in the sys.databases status is "LOG_BACKUP". Or check the size of the logfile, and shrink it when deemed needed.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 26, 2009 at 5:02 pm
shivaram challa (3/26/2009)
Now, if taking log backups isn't needed, then you can do a "dbcc Shrinkfile" on the logfile when the "Log_reuse_wait_desc" in the sys.databases status is "LOG_BACKUP".
You would not be able to shrink the log file unless you truncate it. When the sys.databases "log_reuse_wait_dec" coulmn status is "LOG_BACKUP", then you should first take a log backup which truncates the log and then shrink the log file.
March 26, 2009 at 5:24 pm
The only time we've used a second log file is due to temporary space issues immediately prior to a large transaction. We removed it as soon as practical afterwards, as the reason for the blowout was a one-off change.
I've never seen anything to suggest that the tempdb log should be treated any differently. We did a very quick test on it soon a couple of years ago when we were introducing snapshot locking for a large database we were migrating from SQL2k to SQL2k5: having one tempdb data file per processor core did make a measurable difference for our couple of tests (~5-10%) but we didn't see any measurable difference between having one, two or one-per-core tempdb log files. This could never be considered a definitive test but the lack of measurable difference meant we didn't bother investigating it further.
March 26, 2009 at 5:30 pm
Yes, that's true number of tempdb data files should be equal to the number of core CPU's which gives you considerable performance and also the data files should be of equal size.
There is no performance difference having more than two log files either for tempdb or application databases.
Check this one out for Storage Engine Top10 best practices by the storage engine guys:
http://technet.microsoft.com/en-gb/library/cc966534(loband).aspx
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply