|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
automatical grow does not workHi 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 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. Show quote "eXavier" <f***@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 ? Show quote "Uri Dimant" <u***@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" <f***@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 > > > > > > 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 Show quote "eXavier" <f***@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" <u***@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" <f***@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 > > > > > > > > > > > > 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. -- Show quoteAndrew J. Kelly SQL MVP "eXavier" <f***@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 Show quote "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" <f***@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 > > > > > > 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 -- Show quoteAndrew J. Kelly SQL MVP "eXavier" <f***@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... >> 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" <f***@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 >> > >> > >> >> > > 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 Show quote "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" <f***@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... > >> 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" <f***@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 > >> > > >> > > >> > >> > > > > > > > If I 'd set max size of log file, does SQL Server reuses the file from If SQL Server were to do that, you could end up in a situation where SQL Server had break your > beggining ? sequence of transaction log backup files. If the db is in full recovery model, the log is emptied when you do log backup. If you don't do log backup, run in simple recovery mode. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "eXavier" <f***@centrum.cz> wrote in message news:ufn39WjWFHA.3240@TK2MSFTNGP10.phx.gbl... >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" <f***@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... >> >> 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" <f***@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 >> >> > >> >> > >> >> >> >> >> > >> > >> >> > > > the application hangs if the file Yes, because with a 2GB log and 10% growth you are forcing SQL Server> becomes too large (~2GB 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 --  |
|||||||||||||||||||||||
Other interesting topics