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 |
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
 Posted - 2009-10-21 : 20:45:09
|
| We have the VLDB of Size 163 GB is now under Unrestricted growth setting for the MDF and LDF files.There is notion developed that should be converted to Restricted growth.Under AutoGrowth Window from Database properties File Growth – In Percent it is 10 percent, and Max File Growth selected for Restricted Growth what should I set for the database size of 163 GB and on top.My Other question is what will happen if we set the restricted growth around 400 MB and what are the implications thereafter?Can anyone please analyse these situationsThanks for your help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-21 : 22:59:13
|
| 163GB is not a VLDB. Wait till you get to 500GB or 1TB. You should not be using 10% for the growth rate of the data files. It should be about 250MB or somewhere around there. We always use unrestricted growth except when we want to keep particular data files under 64GB (sparse file issue). With unrestricted growth, you just need to have monitoring in place to alert you when you are nearing running out of disk space. If you instead use restricted growth, your users will get an error when you run out of space inside the data file. So you'd need monitoring in place inside the file.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2009-10-21 : 23:39:04
|
| Thanks Tkizer, the organisation considers 163 GB is VLDB so they want to restrict this growth rate using RESTRICTED.However, may I know what kind of setup to be introduced for alert for this growth rate when it reached its pinnacle?Sorry to bother you I have tried but couldn't get it. Please guide me in this situation.Thanks a lot. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-10-22 : 05:03:39
|
quote:
Originally posted by dbalearner
My Other question is what will happen if we set the restricted growth around 400 MB and what are the implications thereafter?
When the DB reaches 400 GB, your application will stop working and anyone trying to insert data will get errors. Setting a database to restricted growth should only be done when you know that the DB is not going to exceed that size. Do you?I'll echo Tara, 163 GB is quite small as far as databases go.As for the alert, start with sp_spaceused. That will tell you what the used space in the DB is. You should be able to build on that to get your usage reports.--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Database Restricted - Unrestricted