Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Autogrow not working

Author  Topic 

allan8964
Posting Yak Master

249 Posts

Posted - 2011-01-12 : 14:33:22
Hi team,

One of our databses keeps decrease its available disk space. It was set 10% autogrowth when created but for some reason the feature is not working. Now the Space Available is only about 100 Mb. Is there any way to fix that? Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-12 : 15:25:21
It won't autogrow until there is no space left, then it'll grow by 10%. That's how the autogrow feature works. It doesn't keep 10% free at all times, it expands by that setting when there's no space left.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-12 : 15:26:22
By the way, 10% isn't considered a best practice in the case where you've got a sizeable database. If it's more than say 10GB, you'll likely want to change your setting so that it grows by maybe 512MB or similar.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

allan8964
Posting Yak Master

249 Posts

Posted - 2011-01-12 : 16:05:03
Thanks tkizer.
We have much space left in hard disk and other databases still have about 10% available space. Now that database keeps decreasing, is there any way to fix this?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-12 : 16:15:37
You missed my point. I'm not referring to the available free space on the disk. I'm referring to the available free space inside the database files. It will not autogrow until that space has been used.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

allan8964
Posting Yak Master

249 Posts

Posted - 2011-01-12 : 16:56:13
The Available Space shows 0 Mb this morning and we have error in our business server. we did shrink, it gained about 100 Mb, and it keeps going down in number now. If I don't miss your point when it went to 0 Mb this morning, the autogrow shoud work. obviously it did not. Any idea how to fix this?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-12 : 17:42:24
Why did you shrink it?

I am so confused. Show us your database properties.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-01-12 : 18:17:19
quote:
Originally posted by allan8964

The Available Space shows 0 Mb this morning and we have error in our business server. we did shrink, it gained about 100 Mb, and it keeps going down in number now. If I don't miss your point when it went to 0 Mb this morning, the autogrow shoud work. obviously it did not. Any idea how to fix this?



Databases tend to grow as more data is added. You sound like you want this database to stop growing - if so, identify the processes adding data to the database and stop them.

Of course, that is not what you really want to do. If you are concerned about the free space available in the database, then you need to manually grow the data file. Keep monitoring and manually grow it as needed.

Now, before you go and do this - verify that the database is actually growing. Database files will normally have .mdf or .ndf extensions (they don't have to). Log files will normally have .ldf extensions.

Make sure it is not the .ldf file that is growing. If that is the file growing, you probably have an issue with the recovery model being set to full and not running transaction log backups for that database.

Jeff
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-12 : 18:18:10
Also, what error are you getting?
Go to Top of Page
   

- Advertisement -