Understanding database size, available space, initial size, autogrowth
Posted on 2008-10-22
I'm a bit confused with the 4 properties in SQL properties page. Currently our database has following properties;
In General tab:
Database Size: 35000MB
Available space: 9500MB
Initial size for mdf: 29000MB
Initial size for ldf:6000MB
I understood the initial size of mdf +ldf=database size.
When I increase Initial size for mdf by 10MB, the Database Size and Available Space are also increased by 10MB.
Question 1: Is the following formular correct? Database Size - Available Space = Actually used physical disk space for mdf and ldf files.
And also for auto growth, this database is set to autogrowth by 1MB currently which doesn't seem to be right to me because this may result frequent expansion of database. Currently the daily increase of new records being entered is around 20MB. So I guess either I have to turn off autogrowth and manually increase by changing Initial Size For MDF or set higher autogrowth value such as 100MB. We already have enought available space 9GB, but just to know how.
Question 2: To manually increase database size, not using autogrowth, do I just increase Intial Size of MDF in Files tab?
Question 3: How much latency does the end user experience when 100MB is increased by autogrowth? I tried to increase 100MB through Management Studio, but it only takes less than 1 sec, and from end user application I also didn't recognize any freezing or error message . So it seems that from end user, the duration of increasing the size is not much different than when I change from Management Studio. I saw lots of comments from other users here saying auto growth or increasing manually may result performance lag because the database freezes until size increasing process is done, so they recommend to increase size manually during maintenance, but it doesn't seem to be applied to me as I tested.