log file incremental growth question

Last night we upgraded a server with multiple databases to SQL Server
2005 from 2000. While upgrading the msdb database we noticed that the
active process was mostly locked waiting on new transaction log disk
allocation (I forget the wait name). msdb's transaction log was set
to 10% growth. But it never appeared to use 10% of the _current_ log
size, is it possible that the 10% figure is determined at the
beginning of a transaction and not updated as the transaction
proceeds? Monitoring the log file size from the os showed it to be
growing rapidly in small increments.
We know we need to change the default log growth to something more
substantial, but what about the question, is it 10% of the current log
size or 10% of the log as it was when the transaction started?
Thanks.
P. Kline
DBA> We know we need to change the default log growth to something more
> substantial, but what about the question, is it 10% of the current log
> size or 10% of the log as it was when the transaction started?
The way I understand it, it is 10% of the size at the instance the grow occurs. Say the file is 10MB
and a grow occurs. The grow occurs because something need space - but SQL Server do not know at the
moment how much space will be needed *in the end* for that process/transaction.
So at the moment in time it grows to 11MB. Say that the 1 MB wasn't enough and the process still
need more space. This would make the file grow 10% based on the what is now current size: 11MB =>
grow with 1.1MB. etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<pauldkline@.aol.com> wrote in message
news:b9a8ff5c-8ee0-46ff-8c5e-184665daed04@.72g2000hsu.googlegroups.com...
> Last night we upgraded a server with multiple databases to SQL Server
> 2005 from 2000. While upgrading the msdb database we noticed that the
> active process was mostly locked waiting on new transaction log disk
> allocation (I forget the wait name). msdb's transaction log was set
> to 10% growth. But it never appeared to use 10% of the _current_ log
> size, is it possible that the 10% figure is determined at the
> beginning of a transaction and not updated as the transaction
> proceeds? Monitoring the log file size from the os showed it to be
> growing rapidly in small increments.
> We know we need to change the default log growth to something more
> substantial, but what about the question, is it 10% of the current log
> size or 10% of the log as it was when the transaction started?
> Thanks.
> P. Kline
> DBA