Question Regarding Adding a Secondary Data File.
-
Hi All,
I have a situation and hoping to have a right guidance. I have just recently inherited DB. the DB is of size of 200GB on a drive of size 225 GB. Here we are talking about data drive with a mdf file. Since the growth of the DB we need more space and for some technical reason the storage side can not extend the same drive. However, they can give us a new 200 GB drive.
Now I want to create a secondary file group and associate with a secondary data file (ndf) with it. What I want to know are few questions below.
When the SQL will start utilizing this new Drive of this database?
Will it first consume all the space on the mdf file on the first drive and only when it is full will move towards using the secondary file?
what will happen if I make the secondary data file and file group default?
in Order for use to use NDF file , it is necessary to move large table to secondary file group to free up space on Primary?My apologies for so many question.
Regards,
B -
Update: Based on the reading below (highlighted).
"In order to better design your database structure it is really important to understand how SQL Server works with multiple files within a filegroup.
By default the SQL Server uses a proportional fill strategy across all of the files within each filegroups. When new data is added it is not just written to the first file up until it is full, but new data is spread depending on the free space you have within your files.
A very simple example is when you have two data files within the same filegroup; data_file_1.ndf and data_file_2.ndf. The first file has 100MB free space and the second has 200MB free. When new data is added, the SQL Server first checks the free space allocations within the two files. Then, based on the free space information it has it will allocate one extend in data_file_1.ndf and two extends in data_file_2.ndf and so on. By using this simple striping algorithm both files should be full usually at the same time.
Sooner or later all files within a filegroup will be fully utilized and if automatic file growth is enabled the SQL server will expand them one file at a time in a round-robin manner. It will start expanding the first file and as soon it is full it will move the second file. It will expand it and write data to it, as soon it is full it will move to the next;data_file_3.ndf and so on, starting from the data_file_1.ndf again."
------------------------------------------------------------------------------------------------------------------------------------------------------------------For my space issue requirement, the solution should be to create a secondary file (ndf) within the same file on the new 200 GB drive. restrict the growth of the MDF file to 210GB and enable file growth to unlimited on the new ndf file. The sql server will still write some data to old drive till it reaches the limit and will still continue to write on the new drive.
comments or advise are welcome.
-
If your users can be without the db for some time it might be a better option to get a bigger drive with potential to expand at a later stage, and backup/restore onto the new drive instead of adding a new file.
And you can add more files to the same filegroup - does not need to be an new one. if using a new filegroup you would need to manually allocate some tables/indexes to that fg in order for it to be used.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply