December 21, 2006 at 12:34 pm
Hi,
One of my DB is running out of space and I am considering to add a .ndf with it to battle the situation. The .ndf will be sitting in another drive where we have good space.
Could anyone will tell me, how can I ensure that new data are written in the .ndf only as I want to stop/minimize the growth of the .mdf till new drive is purchased.
Or there is a better alternative to battle this?
Thanks in advance.
Regards
Utsab Chattopadhyay
December 21, 2006 at 12:38 pm
Add the new .ndf file and cap (Restrict the growth) the .mdf file growth.
MohammedU
Microsoft SQL Server MVP
December 21, 2006 at 12:45 pm
You can set the default file to be the NDF in the database properties. Then you can move the tables that experience growth to the new file by recreating their clustered indexes to the NDF file.
December 21, 2006 at 3:46 pm
Hi
Thanks for the helps. One more small question ..
What will happen to the existing tables in the .mdf when I will add data to them? As .mdf is capped, at some time when the file will be full, will the table wntirely move to new .ndf or it will be splitted physically?
Thanks for the helps.
Regards
Utsab Chattopadhyay
December 22, 2006 at 5:30 am
Most likely splitted. Why sql server would move 10gb tables as a whole because the mdf suddenly got full.
December 22, 2006 at 10:38 am
What will happen to the existing tables in the .mdf when I will add data to them?
SQL writes new data to .ndf and .mdf file until .mdf file gets full.
If .mdf file is full then all new data goes to .ndf file and the old data will in .mdf.
SQL will not move any compete tables itself unless you do manually.
MohammedU
Microsoft SQL Server MVP
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply