March 19, 2009 at 8:18 am
Can i add a secondary(ndf) file to a production database and move half of the tables into that ndf file without anydown time.
what would be the easy way to do.
Also I do have indexes on the original table, may be i have to create indexes again on the new ndf file, right?
thanks
March 19, 2009 at 9:12 am
Mike Levan (3/19/2009)
Can i add a secondary(ndf) file to a production database and move half of the tables into that ndf file without anydown time.what would be the easy way to do.
Also I do have indexes on the original table, may be i have to create indexes again on the new ndf file, right?
thanks
moving data to another file without any downtime.... I think not advisable...what if data is inserted into a table which is being transfered....
try cheking the locks applied when data is transfered...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 19, 2009 at 10:18 am
I didnt get what you are trying to say, do we really have any issues moving data between the data files. But stilli can have a downtime thats not a big deal if required.
are there any scripts out there to create a file, move data and create indexes on secondary file.
thanks
March 19, 2009 at 11:37 am
Mike Levan (3/19/2009)
I didnt get what you are trying to say, do we really have any issues moving data between the data files. But stilli can have a downtime thats not a big deal if required.are there any scripts out there to create a file, move data and create indexes on secondary file.
thanks
moving data to a secondary file will have sch-m lock on the table being moved.this will prevent any operation against the table.....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 20, 2009 at 6:25 am
I have a table (200GB) with 8 indexes and want to move this to the new data file. Do i need to recreate all the indexes again on the secondary file then move the data to ndf file and then delete indexes on the old file??
I am not sure how this works, does any have any scripts to do this without having any risk on production.
thanks
March 20, 2009 at 6:32 am
Moving a clustered index means your table is moved.After moving a clustered index you can see sp_help 'tablename' will show that the table is on new filegroup.No need of moving the data again.And no need of dropping the indexes for this.The non-clustered indexes can be anywhere ,only the location of clustered index matters here.
If your table does'nt have a clustered index its a different issue again.Needs to create one clustered index temporaraily and later drop it after moving.
Hope all your tables have clustered indexes.
March 20, 2009 at 6:53 am
And, obviously, be sure you have a full, clean backup before starting. It should be a pretty straight forward process but you never know.
-- You can't be late until you show up.
March 20, 2009 at 6:56 am
Just a point, AFAIK, tables and indexes can only be allocated to FileGroups, not mere Files within those FileGroups.
If you add a new file within an existing group, SqlServer should start to make new allocations on that new file until it's "balanced" with the existing files in the group.
If you want to accelerate that process, then I would think that rebuilding the tables/indexes would do it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2009 at 9:39 am
I am still not sure where to start, can some one script out for me as i have given my requirement. just trying to be safe.
March 20, 2009 at 12:09 pm
Why do you want to do this? What are you hoping to accomplish?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2009 at 7:16 pm
I am just trying to split my 1 TB database into 2 drives so that i can accomodate it effectively without any issues after few weeks as it grows rapidly for 2 more months.
So, as i just have 1 mdf and ldf file for a 1 TB database, now i want to create another ndf file and move some tables of size 500GB into that ndf file which will be place in a different drive. Also i have 8 indexes on that those tables which are to be moved.
I hope i am clear.
thanks
March 20, 2009 at 11:16 pm
Then just add another file and let SQL Server manage this by itself. It will take care of it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 21, 2009 at 7:57 pm
How sql server will take care of moving an existing table to a new ndf file?
March 21, 2009 at 9:33 pm
Did you actually read my previous replies?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 22, 2009 at 6:05 am
yes i did but i am looking for a script which move tables from primary file group to secondary file group.
or i can put it this way..
I would like to change filegroup of an existing table.
thanks
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply