| Author |
Topic |
|
dhjackal
Starting Member
42 Posts |
 Posted - 2009-06-02 : 06:13:55
|
| My understanding of SQL Server database creation and setup is that transaction log files (if more than one exists) are used sequnetially e.g. there is no stripping. My question therefore is ;Is there ANY benefit from having more than one database transaction log file?All comments / thoughts appreciatedDhjackal |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-02 : 07:00:18
|
quote:
Originally posted by dhjackal
My understanding of SQL Server database creation and setup is that transaction log files (if more than one exists) are used sequnetially e.g. there is no stripping.
I should hope not!Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
dhjackal
Starting Member
42 Posts |
Posted - 2009-06-02 : 08:45:36
|
| So? There is no benefit? |
|
|
|
SQLRatankalwa
Starting Member
42 Posts |
Posted - 2009-06-02 : 09:00:16
|
| You should avoid having multiple log files, as per the Microsoft Recommendation, you can place 1 log file per processor.If you certainly know that your log file is going to grow, it is better to allocate a huge space for the log file which is lot more better than just putting the auto growth for the log file, this will give you better performance over having multiple log files.Ratan KalwaSQL Server Professionals User Grouphttp://www.revalsys.com |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-06-02 : 09:34:46
|
You will get no benefits with multiple log files. Log file doesn't fill up proportionally like data files. Log files You should avoid having multiple log files, as per the Microsoft Recommendation, you can place 1 log file per process Also this is incorrect. |
|
|
|
dhjackal
Starting Member
42 Posts |
Posted - 2009-06-02 : 12:08:42
|
| Thanks guys. Maybe I should explain my situation a bit better. Basically I've inherited a whole host of databases that have been created with no regard for any kind of standards and I'm trying to work out why my predecessor set things up in the way they did. My server has 4x CPU's. The databases on this particular instance are all fairly big (200Gb +) and they all have 2x transaction logs which live on the same physical disk. I can't see a reason for the particular setup and agree that having one large trx log would suffice. I just wondered if anyone had any idea why the databases may have been created in this way.Thanks in advancedhjackal |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-06-02 : 12:34:25
|
| Only reason could be both log files are spread out to different disks . The file located on low disk is set to restricted growth at certain points. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-06-02 : 19:13:41
|
quote:
Originally posted by SQLRatankalwa
You should avoid having multiple log files, as per the Microsoft Recommendation, you can place 1 log file per processor.
There's no such recommendation. Microsoft's recommendation is a single log file.You're probably thinking of the 1 data file per CPU recommendation for TempDB (doesn't necessarily apply to user databases)--Gail ShawSQL Server MVP |
|
|
|
dhjackal
Starting Member
42 Posts |
Posted - 2009-06-03 : 07:23:48
|
| Thanks Gail. I think i'll revert back to one single log file. The only reason i could think of was that at some point the log lived on a disk which filled up and to resolve the problem the DBA created a 2nd log file on a 2nd disk. The database was subsequently moved to another server and the log files were put together on the same drive? Makes a little sense (i think). |
|
|
|
|
Multiple trx log files - any benefit?