January 19, 2011 at 8:54 am
Doing some catching up on past QOD's and I don't understand how the number 16 was arrived at. The question was about creating a new transaction log not extending one. According to BOL in the Transaction Log Physical Architecture section:
The size or number of virtual log files cannot be configured or set by administrators.
Thanks to anyone who can provide clarification on this.
"Beliefs" get in the way of learning.
January 19, 2011 at 8:58 am
I believe I learned the formula applies to both extensions and initial builds.
January 19, 2011 at 9:47 am
GRE (Gethyn Ellis) (1/7/2011)
Nice question Steve, Its also good to see how much debate this has generated.
I second that thought, it also started discussions on ASK (the first question was from yours truly :-P):
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
January 19, 2011 at 6:21 pm
Robert Frasca (1/19/2011)
Doing some catching up on past QOD's and I don't understand how the number 16 was arrived at. The question was about creating a new transaction log not extending one. According to BOL in the Transaction Log Physical Architecture section:The size or number of virtual log files cannot be configured or set by administrators.
Thanks to anyone who can provide clarification on this.
You can't control it directly, But if you follow Kimberely's blog post and Steve's advice , and say growth your transaction log by increments of 8GB, that will cause the log to grow by 8GB with 16 VLF's rough 512MB per VLF. If you want a log of 24GB, then grow the file first to 8GB and then another 8GB growth (16GB) and then another 8gb. You should find you have 48 VLF's in your 24GB log.
You can test this for your self, using the DBCC loginfo command.
Gethyn Elliswww.gethynellis.com
January 20, 2011 at 5:47 pm
GRE (Gethyn Ellis) (1/19/2011)
You can't control it directly, But if you follow Kimberely's blog post and Steve's advice , and say growth your transaction log by increments of 8GB, that will cause the log to grow by 8GB with 16 VLF's rough 512MB per VLF. If you want a log of 24GB, then grow the file first to 8GB and then another 8GB growth (16GB) and then another 8gb. You should find you have 48 VLF's in your 24GB log.You can test this for your self, using the DBCC loginfo command.
There's maybe a bit of a trap here - there used to be (and as far I know still is) a bug if you extend by an exact multiple of 4GB. That is why I suggested growing by 7142MB instead of 8096MB in an earlier post.
Tom
January 21, 2011 at 1:52 am
There's maybe a bit of a trap here - there used to be (and as far I know still is) a bug if you extend by an exact multiple of 4GB. That is why I suggested growing by 7142MB instead of 8096MB in an earlier post.
Tom, you are right, there is/was a bug with growths of multiples of 4GB. Paul Randal has a blog post on it, it won't do it on the first attempt but will succeed on the second, Paul's post is here http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx
Gethyn Elliswww.gethynellis.com
January 21, 2011 at 3:18 pm
Thanks for the question.
May 24, 2011 at 7:29 am
Super Que. 😉
Viewing 8 posts - 46 through 52 (of 52 total)
You must be logged in to reply to this topic. Login to reply