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 |
|
shiyam198
Yak Posting Veteran
94 Posts |
 Posted - 2010-12-30 : 00:43:49
|
| Hi,When I create a database using the following command, I specify the size, max size, and file growth. Can you please explain these properties?Max size - I understand that max size as the max size of the data file and if the data grows beyong that limit, the database will give an error or crash. Am i right? if that is the case, for an important system (which would be any live production systems), should we always have unlimited?Size - It is the size of the DB? what does this mean if this has any control over how much data will the file have? My guess is this would allocate the space for this file on the hard drive, which prevent any other files cannot use this space on the hard drive? Am I right?Auogrowth -What does this mean? The MCITP 70-431 book says if you specify 0, it will grow. It makes sense.What happens if I specify 20; Won't the file grow beyond 20 MB? if that is the case, what is the difference between this and max size?If I go to the GUI and check Auotgrowth, I see the following:a) File Growthb) maximum file size.Can someone explain this?It is a shame that 70-431 MS book does not have good explanation on this.Thanks for your time,Shiyam==============================================================CREATE DATABASE ProjectsONPRIMARY(NAME = ProjectPrimary,FILENAME = 'C:\Projects_Data\ProjectPrimary.mdf',SIZE = 100MB,MAXSIZE = 200,FILEGROWTH = 20),................============================================================== |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-30 : 01:14:33
|
| Growth is the amount it grows by when it needs more space - so will grow in 20MB increments.You can either give a max size or rely on monitoring the size of the files. It could be an issue if something causes large growth (commonly the tr log due to poor code) and you run out of space affecting other thngs using the disk. Also can be easier to deal with if you have some free space.Have a look in bol.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
shiyam198
Yak Posting Veteran
94 Posts |
Posted - 2010-12-30 : 01:22:49
|
| Thanks..Unless a company is pressed for space, autho growth value should be bigger, correct ? If it has something like 20 MB, and a query is going to spit out 200 MB additional data, SQL server will try the increment 10 times, which will cause a delay.. Am i right?If i say the value should be bigger, what is a ball park figure? Would be having something like 1 GB make sense?Thanks again,Shiyam |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-30 : 01:49:49
|
| The bigger the value the longer the growth will take.Best is to size the database so that it doesn't need to grow and just use this as a failsafe.The default is 10% I believe which means bigger databases will grow by more.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Create Database