June 8, 2009 at 8:11 am
The Background:
1. I have created Two Transaction Log files(nsLog01.ldf and nsLog02.ldf)
each on 1GB and expandable upto Max. 2GB for my application Database nsDatabase.mdf.
This was done with the intension of avoiding the difficult situation of Transaction Log Full situation, write in the middle of Database being used by N no. of users.
This has worked well for Last many years and I had no problems of Maintenance of Transaction Log File size, its Truncation etc.
2. Again, this was not done for any performance improvement point of view.
3. A daily backup - Complete is taken during inactive time(i.e. in the evening).
My Understanding:
I was under impression that the SQL Server writes Transaction Log in 1st file sequentially and when it is full, it starts writing Transaction Log in 2nd file.
2. When 2nd file is full, it starts overwriting Transaction Log in 1st File.
3. As such, the the Problem of Transaction Log File Full has never been experienced by us in say last many ears.
My Question:
How exactly, SQL Server will use these two Transaction Log Files? Is my understanding correct. Please Guide.
Regards,
Dilip Nagle
June 8, 2009 at 9:13 am
I don't have documentation on this topic, but I can only tell you that I experienced the same behavior when multiple log files are involved.
This shouldn't be an issue, anyway.
Regards
Gianluca
-- Gianluca Sartori
June 8, 2009 at 9:23 am
Dilip Nagle (6/8/2009)
How exactly, SQL Server will use these two Transaction Log Files? Is my understanding correct. Please Guide.
Multiple logs will not have an impact on performance as only one of the files is used at a time for logging. This is mostly used to tackle space issues of log files.
Following article from Kimberly will make it clear
http://sqlskills.com/blogs/Kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx%5B/url%5D
A similar post is at
[url]http://www.sqlservercentral.com/Forums/Topic517439-146-1.aspx"> http://sqlskills.com/blogs/Kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx%5B/url%5D
A similar post is at
http://www.sqlservercentral.com/Forums/Topic517439-146-1.aspx
June 8, 2009 at 10:54 am
Dilip Nagle (6/8/2009)
I was under impression that the SQL Server writes Transaction Log in 1st file sequentially and when it is full, it starts writing Transaction Log in 2nd file.
2. When 2nd file is full, it starts overwriting Transaction Log in 1st File.
3. As such, the the Problem of Transaction Log File Full has never been experienced by us in say last many ears.
If there are two log files, SQL will write to one until it reaches the end of the file, then write to the other. When it reaches the end of the second it will only start overwriting what was in log file 1 if those log records are no longer needed. If they are, then either one of the files will grow or you'll get an out of space error.
Log records are no longer needed once the transaction has been committed and there's either been a log backup (in full/bulk-logged) or a checkpoint (in simple).
If you're in full recovery and aren't backing the log up, the log file will grow until it can't grow anymore. Also a long-running transaction may cause the log file to grow.
Please read through this - Managing Transaction Logs[/url]
btw, there's absolutely no difference in behaviour between having 1 log file of 2 GB or two log files of 1 GB each. You gain nothing by having 2 log files.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply