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 |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2006-06-22 : 08:25:50
|
| It is good practise to try to estimate the max size of a database when creating its initial size.Can this be done way after the database has been created, to minimise automatic growing? Is there any danger in doing this? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-06-22 : 09:37:00
|
| Nope, no danger at all. You can increase the size at any time (disk space permitting) using ALTER DATABASE. You can resize data files and log files independently. Books Online has the syntax. |
 |
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-06-22 : 09:46:14
|
| Thanks |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-22 : 09:58:13
|
but it is good to estimate it so that you get less paging due to frequent growths and be able to estimate/allocate resources for other databases, unless you have one server dedicated to this database  --------------------keeping it simple... |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-06-22 : 10:30:06
|
| Hi Jen,Resources such as what?I'm assuming that the paging you are refering too is corrected by dbcc dbreindex? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-24 : 02:19:10
|
Lots of (particularly if they are small) database growths will fragment the disk.I believe if you specify a "big size" on initial create then SQL and the O/S will try to find contiguous disk space.Either way we physically defrag our database files when we have a suitable maintenance window. (We use a single-file defrag tool from SysInternals)Might be worth considering NOT using DBCC REINDEX (and use DBCC DEFRAG instead) to prevent the tables being moved from one end of the database file to the other! (or indeed the database being extended jsut for the REINDEX).Make sure your DB extension size is set to something sensible - and I don't think that 10% is the answer! On some of our busy servers a 10% size increase on a 4GB database would take the server out of action for 30 minutes - users still piling in trying to use the application, the server trying to extend the file, performance dropping, more and more impatient users pressing RETRY ...  And a 1MB extension size for TEMPDB is not a terribly bright idea either!Kristen |
|
|
|
|
|
|
Changing the size of a DB after it's created