Is it possible to change the initial size of the transaction log?

I have a database, and the log file would appear to have been created with
some default settings (it is 512Kb size, which I believe is the minimum SQL
2000 can allocate, and its auto-grow factor is 10%).
When I insert some binary data into an image column, I can see a whole
series of "log file auto grow" events in Profiler. That's what I'd expect,
but each auto-grow event has a pause of just over 1 second before the next
auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see
a
lot of auto-grow events in response to one insertion, the overall query
execution time is very long.
What does SQL Server have an apparently deliberate delay between auto-grow
events? Surely it can grow the log file and initialise it in multiple chunk
s
together, or even not have to wait between chunks? If the insertion causes
10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
expect?
The obvious solution (I can see you thinking) is to change the auto-grow
factor to a more useful fixed value, such as 10Mb (well, the value is
arbitrary, but I think 10Mb will be good for my situation). Doing so clearl
y
will reduce the auto-grow events, and slash query execution time. Yes, I ca
n
do that, but what I really want to achieve is to eliminate auto-growth as
much as possible by setting a larger initial size for my database transactio
n
log.
Is it possible to change the initial size of a database transaction log? I
have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
works, but only temporarily. The problem here is that my database is using
the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
find that the database is sometimes automatically shrunk, and when that
happens, it returns to the 512Kb size it was initially created with. Very
annoying - I want it to return to a more useful size! Is there any way to
configure auto shrinking to return the database to a specific size (such as
you can with DBCC SHRINKFILE)?
I thought about using sp_detach_db and then sp_attach_single_file_db, but
BOL says that doing so will create a new transaction log, which I presume
will have the same defaults as the last time (and therefore renders this
approach rather pointless). Is there any way to ensure the new transaction
log has a more useful initial size?
OK, so that's quite a few in-depth questions I guess Thanks for reading,
and thanks in advance for any help you can offer!
Robyou would do this in the model database. All subsequent databases (including
tempdb) would be created with this autogrow increment.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob|||Rob Pain wrote:
> I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQ
L
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect
,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can se
e a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chu
nks
> together, or even not have to wait between chunks? If the insertion cause
s
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clea
rly
> will reduce the auto-grow events, and slash query execution time. Yes, I
can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transact
ion
> log.
> Is it possible to change the initial size of a database transaction log?
I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is usin
g
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such a
s
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transactio
n
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob
Turn off auto-shrink... It's really quite pointless to repeatedly
shrink a database and/or log file that you know is going to grow again.
You're just causing excessive file fragmentation, and ultimately
hurting your performance.
If you insist on shrinking, look into the DBCC SHRINKFILE command,
where you have more control over what is done...|||You have autoshrink on, and say that there is a problem with the grow. It is
like saying
"It hurts every time I shoot myself in the foot. How can I shoot myself in t
he foot without it
hurting?"
The solution is to turn off autoshrink and let the log file be the size it n
eed to be to accommodate
your transactions. You might want to check out http://www.karaszi.com/SQLServer/in...dont_shrink.asp
for elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQ
L
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect
,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can se
e a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chu
nks
> together, or even not have to wait between chunks? If the insertion cause
s
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clea
rly
> will reduce the auto-grow events, and slash query execution time. Yes, I
can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transact
ion
> log.
> Is it possible to change the initial size of a database transaction log?
I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is usin
g
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such a
s
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transactio
n
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob|||You really, really don't want to depend upon AUTOGROW and AUTOSHRINK in a
production database. You have no control over when it happens, and the
resulting performance hits may come at inconvenient times for your users.
(Of course, AUTOGROW ON, for a large 'chunk', is still a good safety net.)
Using database properties, you can set the db (and log file) size. Pick a
size large enough to handle a periods work (day, week, month). Then at the
end of each period, check the free space, and if more is needed, expand the
db (and log file) size to a size large enough to handle the expected next
periods work. This can be automated to occur at a time of least activity,
thereby having minimal impact on users.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob

Is it possible to change the initial size of the transaction log?

I have a database, and the log file would appear to have been created with
some default settings (it is 512Kb size, which I believe is the minimum SQL
2000 can allocate, and its auto-grow factor is 10%).
When I insert some binary data into an image column, I can see a whole
series of "log file auto grow" events in Profiler. That's what I'd expect,
but each auto-grow event has a pause of just over 1 second before the next
auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
lot of auto-grow events in response to one insertion, the overall query
execution time is very long.
What does SQL Server have an apparently deliberate delay between auto-grow
events? Surely it can grow the log file and initialise it in multiple chunks
together, or even not have to wait between chunks? If the insertion causes
10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
expect?
The obvious solution (I can see you thinking) is to change the auto-grow
factor to a more useful fixed value, such as 10Mb (well, the value is
arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
will reduce the auto-grow events, and slash query execution time. Yes, I can
do that, but what I really want to achieve is to eliminate auto-growth as
much as possible by setting a larger initial size for my database transaction
log.
Is it possible to change the initial size of a database transaction log? I
have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
works, but only temporarily. The problem here is that my database is using
the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
find that the database is sometimes automatically shrunk, and when that
happens, it returns to the 512Kb size it was initially created with. Very
annoying - I want it to return to a more useful size! Is there any way to
configure auto shrinking to return the database to a specific size (such as
you can with DBCC SHRINKFILE)?
I thought about using sp_detach_db and then sp_attach_single_file_db, but
BOL says that doing so will create a new transaction log, which I presume
will have the same defaults as the last time (and therefore renders this
approach rather pointless). Is there any way to ensure the new transaction
log has a more useful initial size?
OK, so that's quite a few in-depth questions I guess Thanks for reading,
and thanks in advance for any help you can offer!
Robyou would do this in the model database. All subsequent databases (including
tempdb) would be created with this autogrow increment.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob|||Rob Pain wrote:
> I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chunks
> together, or even not have to wait between chunks? If the insertion causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transaction
> log.
> Is it possible to change the initial size of a database transaction log? I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob
Turn off auto-shrink... It's really quite pointless to repeatedly
shrink a database and/or log file that you know is going to grow again.
You're just causing excessive file fragmentation, and ultimately
hurting your performance.
If you insist on shrinking, look into the DBCC SHRINKFILE command,
where you have more control over what is done...|||You have autoshrink on, and say that there is a problem with the grow. It is like saying
"It hurts every time I shoot myself in the foot. How can I shoot myself in the foot without it
hurting?"
The solution is to turn off autoshrink and let the log file be the size it need to be to accommodate
your transactions. You might want to check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp
for elaboration.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chunks
> together, or even not have to wait between chunks? If the insertion causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transaction
> log.
> Is it possible to change the initial size of a database transaction log? I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob|||You really, really don't want to depend upon AUTOGROW and AUTOSHRINK in a
production database. You have no control over when it happens, and the
resulting performance hits may come at inconvenient times for your users.
(Of course, AUTOGROW ON, for a large 'chunk', is still a good safety net.)
Using database properties, you can set the db (and log file) size. Pick a
size large enough to handle a periods work (day, week, month). Then at the
end of each period, check the free space, and if more is needed, expand the
db (and log file) size to a size large enough to handle the expected next
periods work. This can be automated to occur at a time of least activity,
thereby having minimal impact on users.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob

Is it possible to change the initial size of the transaction log?

I have a database, and the log file would appear to have been created with
some default settings (it is 512Kb size, which I believe is the minimum SQL
2000 can allocate, and its auto-grow factor is 10%).
When I insert some binary data into an image column, I can see a whole
series of "log file auto grow" events in Profiler. That's what I'd expect,
but each auto-grow event has a pause of just over 1 second before the next
auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
lot of auto-grow events in response to one insertion, the overall query
execution time is very long.
What does SQL Server have an apparently deliberate delay between auto-grow
events? Surely it can grow the log file and initialise it in multiple chunks
together, or even not have to wait between chunks? If the insertion causes
10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
expect?
The obvious solution (I can see you thinking) is to change the auto-grow
factor to a more useful fixed value, such as 10Mb (well, the value is
arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
will reduce the auto-grow events, and slash query execution time. Yes, I can
do that, but what I really want to achieve is to eliminate auto-growth as
much as possible by setting a larger initial size for my database transaction
log.
Is it possible to change the initial size of a database transaction log? I
have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
works, but only temporarily. The problem here is that my database is using
the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
find that the database is sometimes automatically shrunk, and when that
happens, it returns to the 512Kb size it was initially created with. Very
annoying - I want it to return to a more useful size! Is there any way to
configure auto shrinking to return the database to a specific size (such as
you can with DBCC SHRINKFILE)?
I thought about using sp_detach_db and then sp_attach_single_file_db, but
BOL says that doing so will create a new transaction log, which I presume
will have the same defaults as the last time (and therefore renders this
approach rather pointless). Is there any way to ensure the new transaction
log has a more useful initial size?
OK, so that's quite a few in-depth questions I guess Thanks for reading,
and thanks in advance for any help you can offer!
Rob
you would do this in the model database. All subsequent databases (including
tempdb) would be created with this autogrow increment.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob
|||Rob Pain wrote:
> I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chunks
> together, or even not have to wait between chunks? If the insertion causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transaction
> log.
> Is it possible to change the initial size of a database transaction log? I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob
Turn off auto-shrink... It's really quite pointless to repeatedly
shrink a database and/or log file that you know is going to grow again.
You're just causing excessive file fragmentation, and ultimately
hurting your performance.
If you insist on shrinking, look into the DBCC SHRINKFILE command,
where you have more control over what is done...
|||You have autoshrink on, and say that there is a problem with the grow. It is like saying
"It hurts every time I shoot myself in the foot. How can I shoot myself in the foot without it
hurting?"
The solution is to turn off autoshrink and let the log file be the size it need to be to accommodate
your transactions. You might want to check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp
for elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple chunks
> together, or even not have to wait between chunks? If the insertion causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database transaction
> log.
> Is it possible to change the initial size of a database transaction log? I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob
|||You really, really don't want to depend upon AUTOGROW and AUTOSHRINK in a
production database. You have no control over when it happens, and the
resulting performance hits may come at inconvenient times for your users.
(Of course, AUTOGROW ON, for a large 'chunk', is still a good safety net.)
Using database properties, you can set the db (and log file) size. Pick a
size large enough to handle a periods work (day, week, month). Then at the
end of each period, check the free space, and if more is needed, expand the
db (and log file) size to a size large enough to handle the expected next
periods work. This can be automated to occur at a time of least activity,
thereby having minimal impact on users.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rob Pain" <RobPain@.discussions.microsoft.com> wrote in message
news:26AE11DB-ABBF-497E-9087-C40F5838BF5E@.microsoft.com...
>I have a database, and the log file would appear to have been created with
> some default settings (it is 512Kb size, which I believe is the minimum
> SQL
> 2000 can allocate, and its auto-grow factor is 10%).
> When I insert some binary data into an image column, I can see a whole
> series of "log file auto grow" events in Profiler. That's what I'd
> expect,
> but each auto-grow event has a pause of just over 1 second before the next
> auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can
> see a
> lot of auto-grow events in response to one insertion, the overall query
> execution time is very long.
> What does SQL Server have an apparently deliberate delay between auto-grow
> events? Surely it can grow the log file and initialise it in multiple
> chunks
> together, or even not have to wait between chunks? If the insertion
> causes
> 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd
> expect?
> The obvious solution (I can see you thinking) is to change the auto-grow
> factor to a more useful fixed value, such as 10Mb (well, the value is
> arbitrary, but I think 10Mb will be good for my situation). Doing so
> clearly
> will reduce the auto-grow events, and slash query execution time. Yes, I
> can
> do that, but what I really want to achieve is to eliminate auto-growth as
> much as possible by setting a larger initial size for my database
> transaction
> log.
> Is it possible to change the initial size of a database transaction log?
> I
> have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This
> works, but only temporarily. The problem here is that my database is
> using
> the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I
> find that the database is sometimes automatically shrunk, and when that
> happens, it returns to the 512Kb size it was initially created with. Very
> annoying - I want it to return to a more useful size! Is there any way to
> configure auto shrinking to return the database to a specific size (such
> as
> you can with DBCC SHRINKFILE)?
> I thought about using sp_detach_db and then sp_attach_single_file_db, but
> BOL says that doing so will create a new transaction log, which I presume
> will have the same defaults as the last time (and therefore renders this
> approach rather pointless). Is there any way to ensure the new
> transaction
> log has a more useful initial size?
> OK, so that's quite a few in-depth questions I guess Thanks for reading,
> and thanks in advance for any help you can offer!
> Rob

Is it possible for a table to increase size when deleting records?

No transaction log involved, only the table itself.

Use sp_spaceused "table_name" to check the space used.

It seems the table size actually increased from the beginning to the middle of deletion, at the end of deletion, its size decreased.

Recovery mode set to be simple, autoshrink turned on.

The tables tested are about 50MB ~ several GB in size, all have the same behavior. The size increased about 5%~10%.

Since the deletion is called from another software, I want to know if it is possible for SQL Server to have this behavior or it is absolutely the 3rd party software's issue

Thanks!

First of all, you really should turn auto-shrink off. It causes lots of problems with performance and fragmentation.

You probably are seeing index and table fragmentention during the delete. What I would do would be to turn autoshrink off, then if I was very concerned about table size, I would either ALTER INDEX REBUILD or ALTER INDEX REORGANIZE after a large delete to cleanup and shrink the indexes.

|||When you delete a record in SQL Server, the record normally does not get deleted immediately. What happens is that we set a bit in the record to indicate that the record is deleted (this is called the ghost bit).

There is a background process, ghost cleanup task, that runs every 15 seconds and tries to remove records from pages. For indexes, the ghost cleanup task will also remove pages that get empty. For heaps, the ghost cleanup task will not remove pages that get empty

So if your table is a heap, and you are doing a lot of deletes, it is possible that the size of your table does not get smaller. In case of heaps, we will only deallocate pages when we have a table lock on the heap.

There is one other corner scenario where during deletes a record can actually grow in size. For this scenario, you need to have snapshot isolation enabled on the database. When you delete a record during snapshot isolation, we will ghost the record, but we will add version related information (14 bytes) to the record so that snapshot isolation works correctly. Any ghosted records that are part of active transactions cannot be removed by the ghost cleanup task. So in this case, during a delete, you will actually need more space than before, as we need to store the version information for each record as well, potentially adding 14 bytes per record.

For heaps, this might even get worse. If a record gets ghosted and grows with 14 bytes, we might actually have to forward the record to another page if it does not fit on the current page anymore, meaning that you might see a lot of extra space being used. Again, this only happens when snapshot isolation is turned on and you have a lot of pages in the heap that are almost full. Also, the average record size needs to be less than 32 bytes for this to happen, otherwise we do some things in the engine to avoid the forwarding.

Hope this helps,

Is AUTOGROW info recorded?

Is the before & after size, autogrow start & complete time , or any other
info related to auto-growing of datafiles recorded in an error log / event /
sql log / anywhere?
tia
chris
It's not audited, AFAIK.
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:1AE8AB19-BE65-489A-91BA-F35FC4049D90@.microsoft.com...
> Is the before & after size, autogrow start & complete time , or any other
> info related to auto-growing of datafiles recorded in an error log / event
> /
> sql log / anywhere?
> tia
> chris
|||Chris wrote:
> Is the before & after size, autogrow start & complete time , or any
> other info related to auto-growing of datafiles recorded in an error
> log / event / sql log / anywhere?
> tia
> chris
You can detect an auto grow op in Profiler under Database - Data Auto
Grow /Shrink and Log Auto Grow / Shrink.
David Gugick
Imceda Software
www.imceda.com

Is AUTOGROW info recorded?

Is the before & after size, autogrow start & complete time , or any other
info related to auto-growing of datafiles recorded in an error log / event /
sql log / anywhere?
tia
chrisIt's not audited, AFAIK.
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:1AE8AB19-BE65-489A-91BA-F35FC4049D90@.microsoft.com...
> Is the before & after size, autogrow start & complete time , or any other
> info related to auto-growing of datafiles recorded in an error log / event
> /
> sql log / anywhere?
> tia
> chris|||Chris wrote:
> Is the before & after size, autogrow start & complete time , or any
> other info related to auto-growing of datafiles recorded in an error
> log / event / sql log / anywhere?
> tia
> chris
You can detect an auto grow op in Profiler under Database - Data Auto
Grow /Shrink and Log Auto Grow / Shrink.
David Gugick
Imceda Software
www.imceda.com

Is AUTOGROW info recorded?

Is the before & after size, autogrow start & complete time , or any other
info related to auto-growing of datafiles recorded in an error log / event /
sql log / anywhere?
tia
chrisIt's not audited, AFAIK.
--
-oj
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:1AE8AB19-BE65-489A-91BA-F35FC4049D90@.microsoft.com...
> Is the before & after size, autogrow start & complete time , or any other
> info related to auto-growing of datafiles recorded in an error log / event
> /
> sql log / anywhere?
> tia
> chris|||Chris wrote:
> Is the before & after size, autogrow start & complete time , or any
> other info related to auto-growing of datafiles recorded in an error
> log / event / sql log / anywhere?
> tia
> chris
You can detect an auto grow op in Profiler under Database - Data Auto
Grow /Shrink and Log Auto Grow / Shrink.
--
David Gugick
Imceda Software
www.imceda.com