June 16, 2011 at 10:23 am
Greetz!
I have an existing database which has a single .mdf currently around 65Gb. I would like to add several .ndf files to allow for future growth without bloating this single mdf.
I was able to add the file group 'SECONDARY' by using the Add button on the filegroups tab. However when I attempt to add .ndf files via the Add button I get an error stating that the filename is incorrect. I've attempted this both with and without a name in the File Name field.
In my googling I've found this error also shows up when using Alter Database to add files.
How can I add files to my new filegroup in SL Server 2008?
Thanks!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 16, 2011 at 10:30 am
I'm guessing you are adding the files then trying to add them to the Filegroup, I believe you need to specifiy the filegroup when you create the file. I had run accross this recently and I could simply not move a file form one filegroup to another even on a completely database.
CEWII
June 16, 2011 at 10:34 am
Elliott Whitlow (6/16/2011)
I'm guessing you are adding the files then trying to add them to the Filegroup, I believe you need to specifiy the filegroup when you create the file. I had run accross this recently and I could simply not move a file form one filegroup to another even on a completely database.CEWII
Yes, otherwise it will create the new file in the default filegroup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 16, 2011 at 10:39 am
And then won't let you change it...
CEWII
June 16, 2011 at 10:54 am
Actually the error is when I am using the Add Files functionality.
I right click the database and select properties.
I select 'Files' under the 'Select a page' pane on the properties window.
I click the 'Add' button and enter a new logical name, the file type, specify the newly created filegroup, set the initial size, Autogrowth and the Filename (ending in .ndf but which does not exist yet on the file system)
When I click OK I get the error mentioned in my initial post. I have uploaded a screenshot of the error message i get when clicking 'OK'
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 16, 2011 at 11:08 am
Do you have permissions to the specified drive location?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 16, 2011 at 11:33 am
I am using an account that has elevated permissions but I'm not able to verify that it has permissions on that folder because our RDP is down at the moment and won't be fixed for a few days...until we can get an outage window provisioned. That could be the issue though.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 16, 2011 at 12:22 pm
I just had one of th eSystem Admins who has complete control over the drive/directory and he got the same error when clicking ok. Any idea why this is happening?
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 16, 2011 at 12:27 pm
We figured it out! When you select a path for the file by clicking the ellipsis it gives you a location. We are storing these in the root of the drive. For some reason this was showing up as T: and not T:\. Adding the \ manually soved the problem.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 16, 2011 at 12:47 pm
Wow, thanks for posting that back.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 17, 2011 at 6:41 am
Don't you just love those kind? 🙂
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 17, 2011 at 8:53 pm
I guess I missed something because my db ran out of room. The single mdf on the primary was set to Autogrow and had a size cap at 70Gb. The secondary filegroup contains 5 .ndf with a maxsize of 50Gb.
I was expecting the dbengine to automatically switch to the next filegroup and begin inserts on the first .ndf but instead my insert script errored out.
What needs to be done now so that SQL Server will utilize the second filegroup when the frist one fills up?
Thanks!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 18, 2011 at 2:23 am
To have SQL write simultaneously to multiple files, those files have to be in the same filegroup. SQL will never start using a different filegroup for writes.
Multiple files in one filegroup give you automatically spread out data.
Multiple filegroups lets you explicitly specify what filegroup a table or index goes onto.
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 18, 2011 at 7:11 am
Multiple filegroups lets you explicitly specify what filegroup a table or index goes onto.
So a good practice would be to create a secondary filegroup when a database is created and make that the default filegroup? Would making the file group default automatically place tables into it when tables are created? If I know ahead of time that the user tables will grow very large it sounds like it would be best to create a few files in that group of say 50Gb each, limit the size of each file and turn autogrow on for them?
Once user tables are on a file in the primary file group can they be moved to another filegroup or are you stuck in that group?
Thanks!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 18, 2011 at 12:00 pm
MothInTheMachine (6/18/2011)
Multiple filegroups lets you explicitly specify what filegroup a table or index goes onto.
yes
So a good practice would be to create a secondary filegroup when a database is created and make that the default filegroup?
yes
Would making the file group default automatically place tables into it when tables are created?
yes
If I know ahead of time that the user tables will grow very large it sounds like it would be best to create a few files in that group of say 50Gb each, limit the size of each file and turn autogrow on for them?
If you limit the size, autogrow will stop at that limitation and you may continue to have issues. Make sure you have adequately large files for a couple of years growth and turn autogrow for fail-safe scenarios.
Once user tables are on a file in the primary file group can they be moved to another filegroup or are you stuck in that group?
Thanks!
No. Tables can be moved to different filegroups.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply