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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Autogrow not working
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allan8964
Posting Yak Master

249 Posts

Posted - 01/12/2011 :  14:33:22  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 01/12/2011 :  15:25:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
38200 Posts

Posted - 01/12/2011 :  15:26:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 01/12/2011 :  16:05:03  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 01/12/2011 :  16:15:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 01/12/2011 :  16:56:13  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 01/12/2011 :  17:42:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
819 Posts

Posted - 01/12/2011 :  18:17:19  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 01/12/2011 :  18:18:10  Show Profile  Visit russell's Homepage  Reply with Quote
Also, what error are you getting?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000