January 5, 2010 at 10:45 pm
Hii guys,
I have database of size 250GB( .mdf with Automatic unristricted Growth ON and .ldf file) and drive space is 270GB
now i want to create new .ndf file to new drive location My questions are,
1. can i restrict growth of my mdf file to 250gb?
2. After that if i create new .ndf file on new drive location, will sql server manage to put new data into .ndf file.
Thanx in advance.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
January 5, 2010 at 10:56 pm
Easy way using the GUI.
1. can i restrict growth of my mdf file to 250gb?
Answer: Yes you can restrict the size to 250gb. In SQL Server Management Studio. Right click the specific database. Click properties. In the dialog click Files. For .mdf file, in the Autogrowth column, click the ellipsis, and you will find some options there. It will be very self explanatory.
2. After that if i create new .ndf file on new drive location, will sql server manage to put new data into .ndf file.
Answer: On same dialog as in 1. Same tab as Files, there should be an Add button. Here is you can specify the File location, and its growth parameters.
January 5, 2010 at 11:14 pm
does it require downtime?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
January 5, 2010 at 11:27 pm
No. It doesnt require a downtime. Anyways, always backup the database.
January 5, 2010 at 11:36 pm
thanx a lot
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
January 6, 2010 at 12:13 am
One more thing, if you ever decide to physically move the .mdf file to a different larger drive, that can be done as well but that will definitely require a downtime.
You could detach the file, move the .mdf file to a different drive and then attach it.
January 6, 2010 at 1:55 am
sanketahir1985 (1/5/2010)
2. After that if i create new .ndf file on new drive location, will sql server manage to put new data into .ndf file.
If you create it as part of the primary filegroup, yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2010 at 2:28 am
hey gail,
i was looking for this point only
if i put .ndf file to new filegrp & restricts .mdf file then what will happen?
new data will go in .ndf or new .ndf file will be created?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
January 6, 2010 at 2:36 am
sanketahir1985 (1/6/2010)[hrif i put .ndf file to new filegrp & restricts .mdf file then what will happen?
new data will go in .ndf or new .ndf file will be created?
Neither. If you create an ndf, put it into a new filegroup and don't move objects from primary, then new data will go into the existing tables in the primary filegroup and when the file fils up you'll get errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2012 at 5:48 am
Hi Gail,
I have small query....
While creating Database if i create .mdf, .ndf & .ldf are in three different drive. Than my data should be automatically store in .mdf & .ndf...
It is right or some thing else. Please let me know?
Thanks in advance
Satish
June 26, 2012 at 7:05 am
While creating Database if i create .mdf, .ndf & .ldf are in three different drive. Than my data should be automatically store in .mdf & .ndf...
It is right or some thing else. Please let me know?
Yes and No;
--If you have created MDF and NDF on to same filegroup i.e. PRIMARY (Then limit the max growth setting of one and then once it is filled it will start writing to the next one over.)
--If you have created MDF and NDF on to different filegroup i.e One to Primary and another to Secondary. Then data will go to the separate groups only if your objects are created on those filegroups. If your objects are created only on one filegroup then no matter how many groups you add it will never write to the secondary.
June 26, 2012 at 9:11 am
sql.AB (6/26/2012)
(Then limit the max growth setting of one and then once it is filled it will start writing to the next one over.)
SQL writes to all files in a filegroup in a round-robin manner, you don't need to limit the size of one and wait for it to fill before SQL starts writing into the next.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2012 at 10:45 am
Gail - Is there any article for the same? Wanted to read and update myself. It's better to be late than never.
Thanks,
AB
June 27, 2012 at 11:00 am
sql.AB (6/27/2012)
Gail - Is there any article for the same?
Sure. Books Online. The section Files and Filegroups is probably a good place to start.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply