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.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2007-07-31 : 12:58:36
|
| What is the best suggestion to do if a database is using more than 95% of its space. Should I leave it as is and it will grew when it needs? Or do an action. What action?Is your solution the same for log file as well?Thanks, Canada DBA |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-31 : 13:50:37
|
| If the database is more than, say, 500MB is size then consider changing the AutoExtend from 10% to a fixed extension size. (Extending by 10% of, say, 10GB takes a while, and can cause timeouts etc.)Consider purging old, stale, rows.Consider moving old, stale, data to a new database using a federated horizontal partition. This does not save any space, but makes it easier to move the "new database" to a different server in the future.Unless the database is massive (say >50GB) I wouldn't do anything - other than buy more disk space if necessary.Kristen |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2007-07-31 : 15:52:39
|
| Thanks Kristen!The databases are not that big. For example, one is 277MB and has used 265MB. My third party SQL monitor alarms me that the database space has used more than 95%. Based on what you say, if the size is smale and like in my case 10% is only 27MB, then I can leave it as is but if the database size is big like 1GB or more that it might take longer time to allocate space, then I have to check what is the database growth (by % or MB) and make sure if the allocation doesn't take that long. Did I get you right? Any furter suggestion? Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-31 : 16:12:55
|
| Why don't you just expand it a little so that your tool doesn't alarm you? We aren't talking about very much disk space here. Expansion on 2005 is very quick.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-01 : 01:46:13
|
| "Did I get you right?"Yup. I don't think 10% is a sensible default once the database becomes very big. What you want is to adjust things so that the database does not extend too often - perhaps not more than once a week - to reduce the number of fragments.Kristen |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-08-02 : 14:37:14
|
Tara, by expanding you mean: I manually increase the database size? For example change the 265MB database to 350MB?quote:
Originally posted by tkizer
Why don't you just expand it a little so that your tool doesn't alarm you? We aren't talking about very much disk space here. Expansion on 2005 is very quick.Tara Kizerhttp://weblogs.sqlteam.com/tarad/
Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-02 : 14:46:15
|
| Yes. It is better to manually expand anyway so that you know when it is happening and can have it done during a maintenance window. When the automatic expansion occurs, it could be happening during your busiest time of the day!Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-08-02 : 15:09:41
|
| Excellent! Thanks Tara and Kristen!Canada DBA |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-02 : 23:03:58
|
| Sql2k5 comes with report to show db growth history. |
|
|
|
|
|
|
|
|
Database space is low