September 7, 2009 at 9:42 am
I have created a new file, that points to a different path than my other database files. How do I get the new tables I create to point at the new file, (as I have limited space where my other data files are located), rather than the old ones ?
September 7, 2009 at 9:46 am
jabba (9/7/2009)
I have created a new file, that points to a different path than my other database files. How do I get the new tables I create to point at the new file, (as I have limited space where my other data files are located), rather than the old ones ?
if your file is created within a new filegroup, when you do your create table statement, you can specify the new filegroup which contains your new file.
look at CREATE TABLE in BOL, for full syntax.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 7, 2009 at 11:09 am
creating objects on your new filegroup looks like this:
create table MyGreatTable (i int) on MyGreatNewFileGroup
September 7, 2009 at 12:28 pm
In case, if you have created the new file in the same file group (PRIMARY), then you may stop the grow option on the old file and enable grow option on the new file, will lead to move the data on the new file.
if you have created new file in new file group, then follow the previous posts - create table ... on new file group.
September 7, 2009 at 3:59 pm
jymoorthy (9/7/2009)
In case, if you have created the new file in the same file group (PRIMARY), then you may stop the grow option on the old file and enable grow option on the new file, will lead to move the data on the new file.
This is incorrect, data wouldn't move to new file.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 8, 2009 at 12:56 am
jymoorthy (9/7/2009)
In case, if you have created the new file in the same file group (PRIMARY), then you may stop the grow option on the old file and enable grow option on the new file, will lead to move the data on the new file.
That's only true when it's multiple files in a single filegroup. For multiple filegroups, the tables have to be moved to the new filegroup. Do that by recreating the clustered index onto the new filegroup.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply