September 19, 2011 at 1:00 pm
I know most (85%+) of my 2K plus sql instances, will run on 1 log file on a pair of raid 1 disk very well! When possible.
But, when you have databases running 18 to 48 cores, 64g to 256g of ram and are 450g to 1.8T in size, does that hold true? SHOULD i create only 1 280g log file instead of 4 - 70g log files?
Can a log file (SQL Server 2008r2) be written to by more than 1 thread at a time?
I see a couple postings of "Use only 1 log file", but I see nothing to backup the arguement.
Back in the SQL 2000 days on higher end , high transaction volumes, I started using multiple log files because of warnings and errors on the database.
I have a DW (1.8T) with literaly 3K users. There are 8 log files totaling about 700g. Should I be running on a single log file, with 4g growth?
Looking for documentation, direction, ect, for VLDb, many many cores, ect.
Is anyone running 16+ cores and getting by with 1 log file?
Thanks!
September 19, 2011 at 1:12 pm
One log file. Always and only.
SQL does not ever parallel across log file. It writes to the log file in a serial manner. If there's more than one log file SQL will write to the first beginning to end, then the second, beginning to end, then it'll go back to the first. Given that, there's no performance gain (or any other advantage) in having 5 log files of 10GB over 1 log file of 50GB. It's true even at the very high end.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2011 at 1:22 pm
I know, but there are 10's or hundreds of processes, SQL only writes 1 at a time to a log file. Also, I have 4 log files on a system, truncate them and the all grow round robin. It does not fill 1 file up and then move to the next. Also, we have a process that can write 10g to a log file. The other files continue to grow, while the ETL is running.
I am going to test this again. Make sure what I thought we were seeing in behavior, is the behavior.
Again, looking for something from MS / MSDN / documentation. PLUS, have you ever written to a 700g file? Tried to move it?
I do understand and the files are sequential, that a process will write to a file. Why raid 1 is great. But even Raid1, when on SAN, makes little difference except to those very high end systems. I have not seen one of those with only 1 tran log. I blame my limited exposure to large companies.
My understanding, is that a log file can ONLY be written to by 1 process at a time. if you have many processes writing to files, they will have to que up. Right? If there are several log files, each process will grab a log file that is not in use.
September 19, 2011 at 1:35 pm
devereauxj (9/19/2011)
I know, but there are 10's or hundreds of processes, SQL only writes 1 at a time to a log file. Also, I have 4 log files on a system, truncate them and the all grow round robin. It does not fill 1 file up and then move to the next.
SQL only ever writes to one log file at a time. It does not ever write round-robin to log files.
What you are likely seeing there is that all the log files are full and the space is not reusable. So SQL grows one log file, fills that new space, then grows the next fills that space, then grows the third, etc. It's still only writing to one log file at a time.
Again, looking for something from MS / MSDN / documentation.
Is MS more trustworthy than an MVP with practical experience and fairly deep knowledge of how the log works?
I don't have an MS ref, I do have several very reputable MVP blogs if you are willing to accept them (including one from someone who used to work on the storage engine code)
PLUS, have you ever written to a 700g file? Tried to move it?
I've had 300GB log files. (well, one 300 GB log file for a DB). Yes, I've had to move it, but that's not exactly a regular operation. Had to do it once in 5 years, we took the DB offline for the weekend while we moved the log and data files to new storage. Moving 700 GB is moving 700GB, whether it's in one file or 10.
My understanding, is that a log file can ONLY be written to by 1 process at a time. if you have many processes writing to files, they will have to que up. Right? If there are several log files, each process will grab a log file that is not in use.
No. SQL writes to one log file at a time (per database) no matter how many processes are running at a time. That's why I said one log file always and only. SQL will never write to multiple log files at a time. Round-robin and proportional fill are for data files only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2011 at 1:41 pm
p.s. At the sizes you're talking, there's one valid reason for multiple log files. A single log file cannot be larger than 2TB. So if you're touching that, you'll need 2 (or more) log files solely because of that max size limit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2011 at 1:59 pm
Thank you very much.
I am looking a deeper understanding, you are providing that.
I will do some experimenting. I will change one of our test system we can do production loads on it with one file. (currently runs with 6)
Trust, but verify, right? 🙂
I like knowing how things work and why, not that they "just do".
Just found a couple articles on VLF. If possible, do you allow your very large logs to grow at 4g segments? Does it have to find 4g contiguous blocks? We have 8 to 12 very large systems that push 350g to 700g of transaction log files total. These systems, we generally run logs every 45 to 60 minutes.
Does 1 large tranlog file vs several have any impact on Tranlog backups?
Thanks! There are only 3 of us DBA with more than 2K instances across 6 states. We generally don't get much R&D time. If it breaks, FIX IT NOW : If not broken, LEAVE IT : is the motto here. Not always the best.
September 19, 2011 at 2:18 pm
Preventative maintenance?
Out of interest how often do you run transaction log backups?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 19, 2011 at 4:39 pm
We run log backups ever hr at most on large system. Some of our large heavy transaction databases are every 30 minutes. Those even have a total of 300 to 400g of log files.
September 19, 2011 at 4:42 pm
how long do the log backups take to run, may be worth increasing the frequency further if possible
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 19, 2011 at 5:03 pm
As Gail said, Never use more than 1 log file per database. SQL Server fills log file is sequential order not randomly. Multiple log files is only waste of time and effort...
Thank You,
Best Regards,
SQLBuddy
September 19, 2011 at 5:45 pm
Our logs are located on very fast San disk pools with 146g and solid state. The backups are on 600g san drives and then moved to tape. Depending on the time of the day, 25 seconds to 40 minutes.
I have devised a test based on 750g data / 285g worth of logs, just over 1T total. Both sit on 146/Solid State disk pool, different arrays. I will post the test and test results between 6 - 50g logs and 1 - 300g log file.
I am looking to see if there is any benefit from 1 log file vs more than one.
No, I am not argueing what is labled best practice, or that performance should be the same, just need to document and test. Without it, won't be able to change current production instances. At a minimum, have to show there is no degradation of performance.
Let me throw this out there. I have been running multiple logs since SQL 2000 (2003 / 1st prod instance). I have never had any errors, warnings or messages. Never NOT been able to restore database, backup a database,ect. I have performed more than 1000 Prod to Dev resotres
If performance is the same : Is there any technical reason to run 1 log file? Is there any technical reason NOT to run multiple log files?
thx!
September 19, 2011 at 6:07 pm
Having multiple log files for a database will not give you any errors or warnings.. Just wastage of space and increasing the complexity...
It's like buying 10 marriage dresses for ones wedding ceremony.. At any time one can wear only one marriage dress even though he has 10 dresses.. Having additional dresses doesn't in any way improve or decrease the value of the bride\bridegroom... 🙂
This behavior is by the design of SQL Server architecture.. It doesn't matter how many log files you have bcs at any point of time SQL server can use only one log file...
But testing the knowledge and confirming things is a very good practice..
Thank You,
Best Regards,
SQLBuddy
September 20, 2011 at 2:40 am
devereauxj (9/19/2011)
Let me throw this out there. I have been running multiple logs since SQL 2000 (2003 / 1st prod instance). I have never had any errors, warnings or messages. Never NOT been able to restore database, backup a database,ect. I have performed more than 1000 Prod to Dev resotres
Of course not. SQL can handle up to a couple thousand log files (iirc), but unless you're hitting the size limit of a single log file, there's just no reason to do so.
If performance is the same : Is there any technical reason to run 1 log file? Is there any technical reason NOT to run multiple log files?
If performance is the same, why would you want to create more than one? What's the point? You gain nothing except increased complexity (more files to manage if restoring/attaching)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply