August 25, 2009 at 6:43 am
I have a database of 175-180 GB (full recovery, log files backup every hour + full backup) the auto growth are set to 50 MB both for data and log files. Im purging data nightly so the size are pretty stable of the database. Two times the last weeks the database has just auto growth with 50 MB every second until the disk are filled without any reason. When looking at the disk space usage the database has now 13% free (=+20GB) after the growth. The time for the auto growth arent related when the backup of log files (every hour) or the full backup (22:00).
e.g.
Event Start Ms (MB)
Data File Auto Growth 8/23/2009 05:03:34 PM 550 50.00
Data File Auto Growth 8/23/2009 05:03:34 PM 603 50.00
Data File Auto Growth 8/23/2009 05:03:33 PM 566 50.00
Data File Auto Growth 8/23/2009 05:03:32 PM 536 50.00
Data File Auto Growth 8/23/2009 05:03:31 PM 683 50.00
Data File Auto Growth 8/23/2009 05:03:30 PM 533 50.00
Data File Auto Growth 8/23/2009 05:03:30 PM 450 50.00
Data File Auto Growth 8/23/2009 05:03:29 PM 510 50.00
Data File Auto Growth 8/23/2009 05:03:29 PM 430 50.00
Data File Auto Growth 8/23/2009 05:03:28 PM 470 50.00
Data File Auto Growth 8/23/2009 05:03:27 PM 496 50.00
Data File Auto Growth 8/23/2009 05:03:27 PM 473 50.00
Data File Auto Growth 8/23/2009 05:02:19 PM 596 50.00
Data File Auto Growth 8/23/2009 05:02:18 PM 500 50.00
Data File Auto Growth 8/23/2009 05:02:17 PM 453 50.00
Data File Auto Growth 8/23/2009 05:02:17 PM 496 50.00
Data File Auto Growth 8/23/2009 05:02:16 PM 523 50.00
Data File Auto Growth 8/23/2009 05:02:15 PM 600 50.00
Data File Auto Growth 8/23/2009 05:02:15 PM 543 50.00
Data File Auto Growth 8/23/2009 05:02:14 PM 553 50.00
Data File Auto Growth 8/23/2009 05:02:13 PM 606 50.00
Data File Auto Growth 8/23/2009 05:02:12 PM 666 50.00
Data File Auto Growth 8/23/2009 05:02:11 PM 520 50.00
Data File Auto Growth 8/23/2009 05:02:11 PM 506 50.00
Data File Auto Growth 8/23/2009 05:02:10 PM 616 50.00
Data File Auto Growth 8/23/2009 05:02:09 PM 636 50.00
Data File Auto Growth 8/23/2009 05:02:08 PM 540 50.00
Between 05:02:08 PM and 05:43:46 PM it did 587 auto growths of 50 MB = 28 GB.
After this "auto growth race" the database is still growing regular even if there are +20GB of free space in the database file.
There are no jobs running on the server and I cant figure out whats going on. Have anyone else seen this before?
August 25, 2009 at 7:16 am
is TLOG growing? look for a long standing transaction.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 25, 2009 at 7:25 am
This is strange!
But why did you give the autogrowth size as just 50MB when ur database is around 200GB, why dont you change it to 1GB or 1000MB.
I assume something like: bulk operation, index rebuild,udate stats etc going on in the background and the db is trying to adjust it with db growth.
Try changing it to 1GB and see if there is any diff, also check the size of all the individual tables and check for reserved space.
I had similar problem earlier, my db was growing at 150MB every 3 hrs and a single table was a culprit with more than 8gb as reserved space.
Good Luck!Pavan.
August 25, 2009 at 7:29 am
No, the last time the log file growth was 8/19/2009 07:08:48 AM and it was with 50 MB as configured. The current log file are 8 GB and have 98% free space.
August 25, 2009 at 7:35 am
shrink the log file and it will be back to around 100MB.
August 25, 2009 at 7:40 am
Yes, I know that. The log file are OK and arnt the issue.
The size of db arent any issue either I know how to shrink the database file. The question are why its happen at all.
September 14, 2009 at 2:02 am
Hi, I still have this issue that the data file grows until the disk are filled. What I recently did was to drop all index and the data file went down to around 80 GB (from 177 GB) with out the indexes. After I shrinked the data file I re-added all indexes (around 15) one by one which takes around 1 hour each. During that time the data file was growing regular when needed by 1 GB as set. But during adding the 11th index the data file started to grow by 1 GB until the disk was filled. Now I have a data file which uses all available disk again and the data file have 24 GB free space inside.
I wonder if the uncontrolled automatic growth issue are on the space allocation for indexes? Then indexes I have are pretty big compared to the data size. The indexes use to be 60% of the data file size and when everything are shrinked the data size are 180GB. I dont know if thats normal or not?
P.H.
September 15, 2009 at 4:42 am
Every database needs some free space. As a minimum, you will need enough free space to hold a copy of your largest table plus its indexes, so that you can do an index rebuild on this table. It is rare that you can get away with less than 10% free space in your database, and often 20% is normal.
When you added the index that caused the problem, was the index clustered? (Just about every table should have a clustered index, unless you have a very specific situation where this is not helpful.) When you add a clustered index to a table, SQL Server has to create a copy of the table in your database while it builds the index. It may be this work that is causing your database to grow.
You say that your database grows at a particular time in the week. It is worth rechecking if an index rebuild is running at this time.
If you run a DBCC DBREINDEX without limiting the operation to a single table, then you can find that free space grows to 50% of your database size. If you run any index rebuilds, always limit the operation to one table at a time. If you rebuild indexes on your largest table while other index rebuilds are running or you will need more free space than if the largest table is run stand-alone.
BTW, I completely agree with other posts that a growth factor of 50MB on a 200GB database is far too small. A growth factor of 1GB would be more reasonable for a DB of this size, or even up to 20GB if 'instant file initialization' is active.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 16, 2009 at 8:40 am
1) the log shrink was a bad recommendation
2) 50mb growth is too low
3) you mentioned no jobs were running. how did you verify this? did you run sp_who2 active and examine every active spid's execution?
4) what was the definition of the 11th index - the one that (perhaps coincidentally perhaps not) you were building when the db started to grow indefinitely?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 22, 2009 at 10:08 am
What is the value of the fill factor?It can be culprit sometimes.
Pavan.
October 21, 2009 at 8:40 am
I have been trying to find out how to view the "logs" when data file's grow, what tool did the person asking the question use to see the data file growing, SQL Profiler?
Sincerely,
Sonal.
October 21, 2009 at 10:30 am
sonal_anjaria (10/21/2009)
I have been trying to find out how to view the "logs" when data file's grow, what tool did the person asking the question use to see the data file growing, SQL Profiler?Sincerely,
Sonal.
Profiler event 93 is the Log File Autogrowth event to capture.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 21, 2009 at 11:58 am
What version of SQL? I think I had something similar in SQL 2005 with mega users and small autogrowth.
This is interesting. Do you have scripts to create database, populate database with test data, drop and add indexes, etc? I'd like to play with it and attempt to duplicate.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply