March 22, 2009 at 10:44 am
You still have not provided any reason to take this more difficult approach.
What are your real Goals in this?
[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 11:22 am
Mike Levan (3/22/2009)
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
Do you know SQL Server does storage load balance for you?
As already pointed at for others in this very same thread just add datafiles and let SQL Server take care of it.
If you insist in moving your table to a different "drive" what you have to do is...
1- Create a new filegroup
2- Be sure files on new filegroup are sitting in whatever drive you want
3- Rebuild your table's clustered index on new filegroup -table will go there 😉
4- Rebuild other indexes
_____________________________________
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.March 22, 2009 at 1:52 pm
when you say REBUILD clustered index does it means drop constraint and re create it or do we need to use REBULD syntax, if so i know creating index on filegroup but when you use rebuild syntax how can we do with filegroup specific?
March 22, 2009 at 2:47 pm
RBarryYoung (3/22/2009)
You still have not provided any reason to take this more difficult approach.What are your real Goals in this?
I am still waiting for an answer to my questions, Mike.
[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 23, 2009 at 5:11 am
If you add an additional file to an existing file group, you have no control over what data SQL Server will put in the new file.
When any existing database object needs a new extent of disk space, SQL Server will allocate that extent from the file that has the most free space. Therefore over time (assuming both files are the same size) your data will be distributed across both files. You can speed up this process by rebuilding cluster indexes, which will cause the extents that make up the rebuilt index to be in the file with the most free space.
If you do not understand the concept of extents, search in Books Online (BOL) or Google.
If you create a new file group, you can control which tables are in the new file group. You should have a strategy for what tables you put in the new file group (e.g. Move dimension and aggregate tables to the new file group, leaving fact tables in the original file group).
To move a table with a cluster index to a new file group, issue a ALTER TABLE and specify the file group that should host the cluster index. Then issue a REBUILD INDEX and the rebuilt index will be created in the new file group. To move a table without a cluster index, thje simplest option is to define a cluster index on the table for the duration of the move and then drop it after the move is complete.
If you do not know the syntax of ALTER TABLE to specify the required file group, look in BOL.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 23, 2009 at 8:17 am
I don't see how you can move the tables to the another file without redefining the objects.
The steps to recreate it are as follows:
--step1: Create a new filegroup
USE [master]
GO
ALTER DATABASE [XXXXX] ADD FILEGROUP [SECONDARY]
GO
--step2: create a new .ndf file and assign to the filegroup secondary
ALTER DATABASE [XXXXX] ADD FILE ( NAME = N'Tempe2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\XXXXX_2_data.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [SECONDARY]
GO
--Step3: create a new table or recreate a table or an index on SECONDARY filegroup.
USE [XXXXX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[testtable2](
[test] [nchar](10) NULL
) ON [SECONDARY]
I'm sure you can carry it further with appropriate modifications.
Thanks,
Singh.
March 23, 2009 at 9:20 am
Sorry, the command is not ALTER TABLE.
It is CREATE INDEX ExistingTable.ExistingIndex ON NewFilegroup WITH (DROP_EXISTING=ON)
See BOL CREATE INDEX (Transact-SQL).
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 7, 2012 at 4:30 pm
I'm coming to this thread really late, but I wanted to thank you for your answer and to say that if I were looking to hire a DBA and came across this thread while google searching RBarryYoung I'd drop his resume in the trash can so fast..... ugh I hate unhelpful people.
EdVassie (3/23/2009)
If you add an additional file to an existing file group, you have no control over what data SQL Server will put in the new file.When any existing database object needs a new extent of disk space, SQL Server will allocate that extent from the file that has the most free space. Therefore over time (assuming both files are the same size) your data will be distributed across both files. You can speed up this process by rebuilding cluster indexes, which will cause the extents that make up the rebuilt index to be in the file with the most free space.
If you do not understand the concept of extents, search in Books Online (BOL) or Google.
If you create a new file group, you can control which tables are in the new file group. You should have a strategy for what tables you put in the new file group (e.g. Move dimension and aggregate tables to the new file group, leaving fact tables in the original file group).
To move a table with a cluster index to a new file group, issue a ALTER TABLE and specify the file group that should host the cluster index. Then issue a REBUILD INDEX and the rebuilt index will be created in the new file group. To move a table without a cluster index, thje simplest option is to define a cluster index on the table for the duration of the move and then drop it after the move is complete.
If you do not know the syntax of ALTER TABLE to specify the required file group, look in BOL.
June 8, 2012 at 3:26 am
grummle (6/7/2012)
I'm coming to this thread really late, but I wanted to thank you for your answer and to say that if I were looking to hire a DBA and came across this thread while google searching RBarryYoung I'd drop his resume in the trash can so fast..... ugh I hate unhelpful people.
That would be your loss, not his.
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 8, 2012 at 11:03 am
Oh bother. I hate when I'm in the wrong. I missed the first page of the thread.
Sorry RBarryYoung.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply