automatical grow does not work

Hi all,
we encountered problem on testing environment that database does not grow
even though it is configured to grow automatically. There is SQL Server 2000
installed on two Windows Server 2003 in cluster. Application runs on its
dedicated WinServer using ADO.NET for data manipulation. When data file is
full (or it remains only small free space ~2MB), all application's db
requests fail with timeout. When I manually enlarge data file, application
starts to work again.
Is there any known issue regarding to automatical grow of database. Does
anyone face this problem ?
eXavierHi
How big is your database?
It might take time .For example if the database is 20GB and you set a 20%
growth rate that means if the database grows it will increase by 2GB. This
may or may not be what you want.
"eXavier" <fhns@.centrum.cz> wrote in message
news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> we encountered problem on testing environment that database does not grow
> even though it is configured to grow automatically. There is SQL Server
2000
> installed on two Windows Server 2003 in cluster. Application runs on its
> dedicated WinServer using ADO.NET for data manipulation. When data file is
> full (or it remains only small free space ~2MB), all application's db
> requests fail with timeout. When I manually enlarge data file, application
> starts to work again.
> Is there any known issue regarding to automatical grow of database. Does
> anyone face this problem ?
> eXavier
>|||After restore, the database is about 1.7 GB, I tried to set growing to
both - fixed amount and percentage factor with no success. When I did SQL
insert from QA - it took about 3 minutes but the file increased. I have only
60 seconds timeout on ADO connection string, may it be that timeouting of
connection prior to data are inserted could cause not performing of file
growth ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gBBktgWFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Hi
> How big is your database?
> It might take time .For example if the database is 20GB and you set a 20%
> growth rate that means if the database grows it will increase by 2GB.
This
> may or may not be what you want.
>
>
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
grow
> 2000
is
application
>|||Well, what is about your hardware? Do you have enough space for the database
file?
How many users connected to the database? Have you checked are there
blocking,locking?
Try to change a TimeOut command to 0
"eXavier" <fhns@.centrum.cz> wrote in message
news:OS9EcBhWFHA.3840@.tk2msftngp13.phx.gbl...
> After restore, the database is about 1.7 GB, I tried to set growing to
> both - fixed amount and percentage factor with no success. When I did SQL
> insert from QA - it took about 3 minutes but the file increased. I have
only
> 60 seconds timeout on ADO connection string, may it be that timeouting of
> connection prior to data are inserted could cause not performing of file
> growth ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gBBktgWFHA.2572@.TK2MSFTNGP14.phx.gbl...
20%
> This
> grow
Server
its
file
> is
> application
Does
>|||First off you should never rely on Autogrow to do the work for you. You
should always have plenty of free space in the files so Autogrow never kicks
in. If a thread invokes the autogrow process and during the growth the user
connection times out it can roll back the growth that it did as well. So it
may not look like it grew when in fact it did and then shrunk again. Never
use the default of 10% on anything over a few hundred MB's. And make it just
a few MB so as you are sure it will grow in less than 30 seconds if you have
a slow disk subsystem.
Andrew J. Kelly SQL MVP
"eXavier" <fhns@.centrum.cz> wrote in message
news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> we encountered problem on testing environment that database does not grow
> even though it is configured to grow automatically. There is SQL Server
> 2000
> installed on two Windows Server 2003 in cluster. Application runs on its
> dedicated WinServer using ADO.NET for data manipulation. When data file is
> full (or it remains only small free space ~2MB), all application's db
> requests fail with timeout. When I manually enlarge data file, application
> starts to work again.
> Is there any known issue regarding to automatical grow of database. Does
> anyone face this problem ?
> eXavier
>|||Thank you for your explanation. In fact, I was facing similar issue, when
transaction log was growing too fast and when it was about 2 GB, the
application also started to timeout. To avoid this I created job for
shrinking transaction log every midnight. Is it good practice to do some
file size check/grow job yet?
eXavier
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
> First off you should never rely on Autogrow to do the work for you. You
> should always have plenty of free space in the files so Autogrow never
kicks
> in. If a thread invokes the autogrow process and during the growth the
user
> connection times out it can roll back the growth that it did as well. So
it
> may not look like it grew when in fact it did and then shrunk again.
Never
> use the default of 10% on anything over a few hundred MB's. And make it
just
> a few MB so as you are sure it will grow in less than 30 seconds if you
have
> a slow disk subsystem.
> --
> Andrew J. Kelly SQL MVP
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:ekIyJogWFHA.2740@.TK2MSFTNGP14.phx.gbl...
grow
is
application
>|||Why are you shrinking the files? You just proved to yourself that the
growth was causing problems and yet you still continue to shrink. Have a
look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"eXavier" <fhns@.centrum.cz> wrote in message
news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Thank you for your explanation. In fact, I was facing similar issue, when
> transaction log was growing too fast and when it was about 2 GB, the
> application also started to timeout. To avoid this I created job for
> shrinking transaction log every midnight. Is it good practice to do some
> file size check/grow job yet?
> eXavier
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23f3AFLhWFHA.3176@.TK2MSFTNGP12.phx.gbl...
> kicks
> user
> it
> Never
> just
> have
> grow
> is
> application
>|||I shrink the transaction log because the application hangs if the file
becomes too large (~2GB). There is enough space on disk array (~40GB).
Probably it could be the same problem as with data file - timeouted
connection together with autogrow, I'll have to test it.
If I 'd set max size of log file, does SQL Server reuses the file from
beggining ? Now it looks like the log size is still increasing, appending
data at the end - this is the reason for my daily shrink. (I left default
values for auto grow - i.e. unrestricted grow by 10%, I'll change it.). What
are best practices for setting size / max siz of database/log. I expect data
file size could be 5-10 GB in my application, but don't have idea about
optimal size of transaction log and if it is better to set max size or
rather letting it grow unrestricted.
The application is highly transactional, but there are typically not much
transactions running in parallel - it processes text-based business messages
mapping them to database - several clients then operates on data typically
changing states again in DB transaction.
Do you have any tips or could you recomend some articles or books on this
topic ?
Thanks a lot
eXavier
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwlMFsiWFHA.796@.TK2MSFTNGP09.phx.gbl...
> Why are you shrinking the files? You just proved to yourself that the
> growth was causing problems and yet you still continue to shrink. Have a
> look here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
>
> "eXavier" <fhns@.centrum.cz> wrote in message
> news:eIfscSiWFHA.1796@.TK2MSFTNGP15.phx.gbl...
when
You
So
Server
file
>|||> the application hangs if the file
> becomes too large (~2GB
Yes, because with a 2GB log and 10% growth you are forcing SQL Server
to allocate upto 200MB in a transaction! Best policy is to assess the
correct size under test conditions, then fix the size and leave it. No
shrinking and no autogrow. Log usage should be controlled by the
frequency of log backups, not by shrinking. Shrinking achieves nothing
in a production OLTP environment.
Once you've fixed the size, monitor usage and create a new log file if
you need one rather than increase the size of the existing one. Since
your file system will already be highly fragmented you should probably
start afresh with a new file of the correct size for optimum
performance.
David Portas
SQL Server MVP
--|||> If I 'd set max size of log file, does SQL Server reuses the file from
> beggining ?
If SQL Server were to do that, you could end up in a situation where SQL Ser
ver had break your
sequence of transaction log backup files. If the db is in full recovery mode
l, the log is emptied
when you do log backup. If you don't do log backup, run in simple recovery m
ode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"eXavier" <fhns@.centrum.cz> wrote in message news:ufn39WjWFHA.3240@.TK2MSFTNGP10.phx.gbl...[
color=darkred]
>I shrink the transaction log because the application hangs if the file
> becomes too large (~2GB). There is enough space on disk array (~40GB).
> Probably it could be the same problem as with data file - timeouted
> connection together with autogrow, I'll have to test it.
> If I 'd set max size of log file, does SQL Server reuses the file from
> beggining ? Now it looks like the log size is still increasing, appending
> data at the end - this is the reason for my daily shrink. (I left default
> values for auto grow - i.e. unrestricted grow by 10%, I'll change it.). Wh
at
> are best practices for setting size / max siz of database/log. I expect da
ta
> file size could be 5-10 GB in my application, but don't have idea about
> optimal size of transaction log and if it is better to set max size or
> rather letting it grow unrestricted.
> The application is highly transactional, but there are typically not much
> transactions running in parallel - it processes text-based business messag
es
> mapping them to database - several clients then operates on data typically
> changing states again in DB transaction.
> Do you have any tips or could you recomend some articles or books on this
> topic ?
> Thanks a lot
> eXavier
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uwlMFsiWFHA.796@.TK2MSFTNGP09.phx.gbl...
> when
> You
> So
> Server
> file
>[/color]