July 13, 2007 at 11:39 am
Hello,
I have a SQL 2000 database with approx 10 database files (for an SAP system). Each data file is set to auto grow by 500 mb. When I look at the Task view in Enterprise mgr, I see all of my files are almost all filled up. At what point will SQL automatically grow my data file size another 500 mb? Does it do this for all files at the same time or is there a specific order? Some of my data files are only showing with 1 mb available! There is plenty of physical disk space left so that the files can grow another 500 mb. Any info would be helpful.
July 13, 2007 at 12:40 pm
It autogrows "as needed". Lets say you have a database that is 100 MB and set to autogrow 500 MB. You have 1 MB left, but the database isn't used much and may only grow .25 MB a week. This database probably won't autogrow for a month as there is enough space to expand three to four times (three weeks at .25 MB a week is .75 MB, leaving .25 MB still free). But you could have another database the exact same setup (100 MB, 500 MB autogrow, 1 MB left) that grows .5 MB daily. That will autogrow within two days. Likewise, a database with the same setup, but getting 5 MB of data a day will autogrow the first time it gets transactions.
-SQLBill
July 16, 2007 at 3:52 am
The autogrows expands the file when it reaches the maximum sizt that can be stored in the file suppose let us have a datafile which is 900MB and you have allowed it to grow by 10% you have 886MB used and 4MB free and you insert data into the datafile which is more than 80MB then yuor file will grow to 990MB and so on.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 16, 2007 at 5:35 am
providing all the files are in the same filegroup SQL uses a proportional fill system where it tries to keep all the files filled to the same percentage. To do this it will insert data in a 'round robin' fashion. therefore if all your files are the same size (best to define them the same size) they will grow by the 500MB growth setting at about the same time, but the first one will not grow untill all are full, then others will follow soon after
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply