DB does not autogrow

Good Morning,

I am having a problem where I have a process that seems to fill up the database but the database does not seem to grow like it should. The database is set to autogrow by 10%... the database is around 15-16GB and it is right over 16GB when it fails. This happens once a week lately. nothing on the server has changed.

the strange thing is when you look at the database properties through EM it shows as no space available but when you do a shrink file and look at the log and data files there are over almost 2GB free on the data file and 1GB free on the log... it does no make sense...

in order for EM to show there is free space I have to add over 5GB to the data file and 1.5 GB to the log file. then processes continue...

is there something that I am missing?

I saw exactly the same problem years ago with a large DB.

The problem is that the SQL statement that is causing the Auto grow is timing out, so the grow rolls back !

Next statement does the same thing.

Never keep the default 10%, always set to some size that can be acheived in under 10 seconds.

I use 100Mb on data and 10mb on transaction.

You should not really be relying on auto-grow, a DBA should monitor it and manually grow during off-peak time.

|||

Hi William,

the best practice for data files isn't to use the Growth in Percent, but rather the Growth in Megabytes, and select a value that will work for your database.

For example, small databases might set the Growth in Megabytes to 1 Gig, where very large databases may set it to 5 Gigs or more. The reason for this is that the default percent growth (10%) for a 60 Gig database will be 6 Gigs – a large uncontrolled jump at any one time. Setting it to 1 Gig or 2 Gigs will create auto growths in a much more controlled manner.

To size your transaction log files you should do the following:

1-Shrink the log as much as possible.

2-Dump every 10-15 minutes.

3-Monitor its growth over a week - nothing the maximum size.

4-Lastly, dump the log, shrink it one more time to the minimum size and then manually size it for this observed maximum size.

Note: Use Performance Monitor to monitor the performance objects SQL Server Databases ( Log Growths, Percent log Used, and Data File Size).

Regards,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com

|||

I never thought about changing the growth to not be in percent... the database is a "load" database so it typically does not change too much but we have been adding more tasks to it so I am sure it will be growing steadily. I am going to give the change of growth a shot and see how it is...

I am also trying to track down where the shrinking is being done from so once the database is set it will not be shrunk.

Thanks guys... sometimes the easiest solutions are overlooked.

|||

Check the properties of the database, 'options' tab

there is an 'auto shrink' option