February 5, 2008 at 7:57 am
We have SQL Server 2000 (sp3) on Windows 2003.
When I right click database, select properties and click the Data Files Tab. I have 798 MB of space allocated to the data file (.mdf). I have "Automatically grow file" selected with the "By 10 Percent" and "Unrestricted file growth" options selected.
Under View Taskpad it shows:
DB Size: 833 MB
Space Available: 79 MB
Data File: Total 797 MB Used 720 MB Free 77 MB
Log File: Total 36 MB Used 34 MB Free 2 MB
If I have a 10 GB drive, does the options selected above mean that the database's datafile will grow by 10% until it fills up the 10 GB Disk Drive? Or, does it mean it will grow by 10% until it fills up the datafile allocated size of 797 MB or the database size of 833 MB? Since "Unrestricted file growth" option is selected, what is the restriction? Is it the disk drive space, the datafile size (allocated space) or the database size (datafile and log file)?
Is it more efficient to increase the datafile space to a large number up front verses having these options ("Automatically grow file") selected and allowing sql server to manage the file growth? Does increasing the space up front to a large value cause the database backups files to also be larger?
Thanks in advance, Kevin
February 5, 2008 at 11:22 am
The database will increase in size as it is used up to the maximum space available which is your 10 GB drive, the 10% default is acceptable on really small databases but dangerous on databases expected to be large as the growth can compound up to vast sizes, I find its better to gauge expected say monthly/yearly growth depending on database use and add a fixed amount, rather than a percentage as your growth factor. You can manually increase the size if the expansion affects performance, so that you manually grow sizes in periods of least activity. Unless your on top of the growth is safest to keep the automatically grow option on.
Your backup file will be a backup of real data not space so the size you set won't affect the backup size, but will affect the size required to restore, so if you add a say 1gb to the datafiles for expansion, you will need that space to do the restore.
February 5, 2008 at 11:30 am
Performance-wise - it is usually better to make your files "big" so that they don't have to auto-grow. The problem with auto-grow is that it will almost invariably happen at the worse possible moment (i.e. when your server is busy), which will cause some delays while the server is busy adding and overwriting the new disk space (It tends to "kick"/time-out user connections during that time from what I've seen). It also helps cut down on OS-level file fragmentation.
So - if you need to, give your data and log files a LOT of space, and leave the auto-grow on just in case you don't keep a good eye on it. And yes - I would move away from the % method of growing the file - go for a fixed amount instead.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply