Moving tables between filegroups
-
Okay, having been inspired to try new things -- I just went through the 70-228 training class and picked up a few hints. And also because I realized how easy it would be to change our historical acctg data.
My goal is to add a secondary data file to the historical data and then make the secondary read only.
Now the problem:
Does anyone have a script to move all non-system tables to the secondary file?
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
No script handy, but simply create clustered indexes for each table you wish to move, specifying your secondary file. If you already have a clustered index, drop it and recreate on secondary.
Steve
-
Would that actually move the tables? Or just because the indexes aren't updatable?
This isn't a homegrown DB and there are over 700 tables. I don't want to try play with that many by hand.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
It'll move the tables because the clustered index IS the data.
As to manually doing it, don't. Write a script to handle it. You can check sysindexes for clustered indexes, giving you all the tables that need to have the indexes recreated. For all others, you don't need to drop first.
Steve
-
Yes, the leaf level of a clustered index is actually the data itself. You might also want to specify WITH DROP EXISTING in the CREATE INDEX statement. Should speed up things when there are also nonclustered indexes present.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Great,
But does anyone have the suggestions of a script? And what do you do about tables with no indexes?
I know the standard is that every table should have an index, but just glancing - some don't. Again, I didn't develop the database -- I just get stuck with maintaining it.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
And what do you do about tables with no indexes?
Create one to move the data and drop it afterwards. Still faster than using EM.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Once the Index + Data gets moved to a new place, how can I verify this?
Is there any SQL that tells me that the data has really moved?
paul -
Okay, to give you an idea of what I'm dealing with.
I used the EM to script out all tables, indexes, views and constraints, triggers with drops and comments. My script runs to 95,870 lines!
This is a delivered DB. I need to shift the user tables. Again some tables don't have indexes and trying to figure out what to do on them would probably be a nightmare because this DB uses a lot of composite keys.
I have no desire to know this DB that intimately.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
>>Once the Index + Data gets moved to a new place, how can I verify this?
Is there any SQL that tells me that the data has really moved?<<
select fg.groupid
, fg.groupname
, o.name as objectname
, ix.indid, ix.name as indexname
, f.filename as filenames
from sysobjects o
inner join sysindexes ix
on o.id = ix.id
inner join sysfilegroups fg
on ix.groupid = fg.groupid
inner join sysfiles f
on f.groupid = fg.groupid
where o.xtype = 'u'
order by fg.groupname, o.objectname
* Noel -
>> I have no desire to know this DB that intimately <<
Then you shouldn't be doing DBA work on it
* Noel -
I have scanned its figures
I know it's legs I have looked at some of the larger bits. 
And know how much it bytes. 
But some other company built this baby
and the overall all application. (And is now owned by a 5000 lb. gorilla whose name starts with M.)We have 1 support incident left for the year. And so far it hasn't broken badly enough that I want to talk to the gorrilla.
I don't want to manually scan 700+ tables, many thousands of columns and have to pray it works out on the rebuild.
Does SQL2K5 have "ALTER TABLE [TableName] SET NEWFILEGROUP [Secondary]"?
IMHO, it should.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Believe me I know the feeling
* Noel -
Yo, Jim!
Just write you a script to step through sysindexes, pulling the ones that have clustered indexes. Have the script create your drop statements and your create statements.
Then write a second script to step through sysobjects and sysindexes, finding the tables that don't have clustered indexes. They only need create statements.
Have a look at the procedure I sent you earlier today. Should give you an idea of what I'm talking about.
Steve
By the way, if you don't already have it, go to this link and get the SQL Server system table map, which will tell you what you're looking at in sysindexes and sysobjects!
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp
-
Don't look too closely at this system table map. The next version will turn several things here upside down.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply