September 18, 2012 at 1:05 pm
I have a relatively large database (1.7)tb on a SS2208rs Enterprise system. The database has two data file, one file is set to auto grow and one is not. The autogrowth is 10g. The drive is filling up obviously 2tb is our max currently. I will be adding a new drive to compensate this in the near future. It is my understanding (the db predates my employment) that the data file with autogrowth turned off was done so when it was the only data file associated with this db, in hopes that all new data would go into file 02 which still has auto growth enabled. The older file is ~740gb and the newer file is ~950gb. My plan is to add a new 800gb drive, relocate the 740gb no growth file to the new drive. My question is what do with the second data file. If I create say 3 other data files and associate them with the db they will grow equally? Will the large file grow as well or wait for the other files to catch up in size? I normally find answers to all my problems on this site, so I am asking the GURUs :-). Is there a better plan for all of this? This db will grow ~1tb a year indefinately, archive and compression issues are the next to conquer. Thanks in advance for any insight
September 18, 2012 at 1:10 pm
read the first part of this
http://msdn.microsoft.com/en-us/library/ms187087%28v=SQL.90%29.aspx
September 18, 2012 at 1:14 pm
Adding additional files are governed by proportional fill ,which means that data is placed equally in all files depending on how much free space is there in each. If you have a table that is accounting for this growth then it makes sense to move this table into the new filegroup and have the additional files in this filegroup set to auto grow.
Also you can explore things like compression and even dicuss purging of the data if its an option.
partitioning large tables might also be worth considering at this point.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply