how to move the table to another drive with out taking them to offline?

  • lazy writer

    Ten Centuries

    Points: 1126

    Hi all,

    could you please let me know how to move the table to another drive with out taking them to offline in sql 2k?

    thanks in advance,

    regards,

    Kris

  • Lowell

    SSC Guru

    Points: 323463

    Krisn (4/26/2010)


    Hi all,

    could you please let me know how to move the table to another drive with out taking them to offline in sql 2k?

    thanks in advance,

    regards,

    Kris

    Kris if you alter the primary index/clustered index of a table and move it to another filegroup(which happens to be on another drive), that would move the physical data of a table...is that what you are talking about?

    so you'd add a new filegroup, that happens to be on a different drive,a nd then you could migrate stuff to that file group...a MillionBillionRowTable might take a while, but it would migrate without having to take the server off line.

    the syntax is like this:

    Create index [IX_TallyCalendar] on [dbo].[TallyCalendar] ( DayOfWeek, TheDate ) WITH DROP_EXISTING on [YourNewFilegroup] GO

    and here is a handy snippet of code which generates statements like the above example, in SQL 2000 acceptable syntax:

    Create view V_IXCOL

    as

    select SIK.* , C.name as ColName

    from dbo.sysindexkeys SIK

    inner join dbo.syscolumns C

    on SIK.id = C.id

    and SIK.colid = C.colid

    go

    Declare @NewFG varchar(128)

    set @NewFG = 'YourNewFilegroup'

    print '-- Move NCI Indexes to new FG'

    print '-- keep column-ordinal / order equal to PK column-ordinal / order (asc/desc)'

    select 'Create ' + case when (SIX.status & 2)<>0 then 'UNIQUE ' else '' end + 'index [' + SIX.name + '] on [' + U.name + '].[' + O.name + '] ( '+

    IX1.ColName

    + case when IX2.ColName is null then '' else ', ' + IX2.ColName end

    + case when IX3.ColName is null then '' else ', ' + IX3.ColName end

    + case when IX4.ColName is null then '' else ', ' + IX4.ColName end

    + case when IX5.ColName is null then '' else ', ' + IX5.ColName end

    + case when IX6.ColName is null then '' else ', ' + IX6.ColName end

    + case when IX7.ColName is null then '' else ', ' + IX7.ColName end

    + case when IX8.ColName is null then '' else ', ' + IX8.ColName end

    + case when IX9.ColName is null then '' else ', ' + IX9.ColName end

    + case when IX10.ColName is null then '' else ', ' + IX10.ColName end

    + case when IX11.ColName is null then '' else ', ' + IX11.ColName end

    + case when IX12.ColName is null then '' else ', ' + IX12.ColName end

    + case when IX13.ColName is null then '' else ', ' + IX13.ColName end

    + case when IX14.ColName is null then '' else ', ' + IX14.ColName end

    + case when IX15.ColName is null then '' else ', ' + IX15.ColName end

    + case when IX16.ColName is null then '' else ', ' + IX16.ColName end

    + case when IX17.ColName is null then '' else ', ' + IX17.ColName end

    + case when IX18.ColName is null then '' else ', ' + IX18.ColName end

    + ' ) WITH DROP_EXISTING on [' + @NewFG + '] ' + char(10) + 'GO '

    from V_IXCOL IX1

    inner join dbo.sysobjects O

    on IX1.id = O.id

    and o.xtype = 'U'

    inner join dbo.sysUsers U

    on O.Uid = U.Uid

    inner join dbo.sysindexes SIX -- no INFORMATION_SCHEMA available for this info

    on IX1.id = SIX.id and IX1.indid = SIX.indid

    and SIX.indid between 2 and 254 -- Select only NCI

    and SIX.name not like '[_]WA[_]%'

    left join V_IXCOL IX2

    on IX1.id = IX2.id and IX1.keyno = 1 and IX2.keyno = 2

    left join V_IXCOL IX3

    on IX1.id = IX3.id and IX1.keyno = 1 and IX3.keyno = 3

    left join V_IXCOL IX4

    on IX1.id = IX4.id and IX1.keyno = 1 and IX4.keyno = 4

    left join V_IXCOL IX5

    on IX1.id = IX5.id and IX1.keyno = 1 and IX5.keyno = 5

    left join V_IXCOL IX6

    on IX1.id = IX6.id and IX1.keyno = 1 and IX6.keyno = 6

    left join V_IXCOL IX7

    on IX1.id = IX7.id and IX1.keyno = 1 and IX7.keyno = 7

    left join V_IXCOL IX8

    on IX1.id = IX8.id and IX1.keyno = 1 and IX8.keyno = 8

    left join V_IXCOL IX9

    on IX1.id = IX9.id and IX1.keyno = 1 and IX9.keyno = 9

    left join V_IXCOL IX10

    on IX1.id = IX10.id and IX1.keyno = 1 and IX10.keyno = 10

    left join V_IXCOL IX11

    on IX1.id = IX11.id and IX1.keyno = 1 and IX11.keyno = 11

    left join V_IXCOL IX12

    on IX1.id = IX12.id and IX1.keyno = 1 and IX12.keyno = 12

    left join V_IXCOL IX13

    on IX1.id = IX13.id and IX1.keyno = 1 and IX13.keyno = 13

    left join V_IXCOL IX14

    on IX1.id = IX14.id and IX1.keyno = 1 and IX14.keyno = 14

    left join V_IXCOL IX15

    on IX1.id = IX15.id and IX1.keyno = 1 and IX15.keyno = 15

    left join V_IXCOL IX16

    on IX1.id = IX16.id and IX1.keyno = 1 and IX16.keyno = 16

    left join V_IXCOL IX17

    on IX1.id = IX17.id and IX1.keyno = 1 and IX17.keyno = 17

    left join V_IXCOL IX18

    on IX1.id = IX18.id and IX1.keyno = 1 and IX18.keyno = 18

    where IX1.keyno = 1

    and not exists (select *

    from sysobjects PK

    where xtype = 'PK'

    and PK.parent_obj = O.id

    and PK.name = SIX.name )

    order by U.name , O.name, SIX.name

    go

    drop view V_IXCOL

    go

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    Your version of SQL Server is 2005 or 2000? You have posted your query in SQL 2005 section but post says "2k"??

    That being said, in 2005 you have some thing called Table Partitions. You create a mdf file on another drive, create partitions that are inline with your current table, move the data in the table to that partition and drop this table if would want.. I am not 100% sure with this approach coz' I have not tried it.. Probably lets wait for some of our SSC bigwigs to guide us from here.

    To know more about table level partitioning - click on any of the following links

    1. Partitioning Blog - BY Pinal Dave[/url]

    2. MSDN - Partitioned Tables and Indexes in SQL Server 2005 - K.L. Tripp

    Cheers!!

  • lazy writer

    Ten Centuries

    Points: 1126

    Hi Lowell and Coldcoffe,

    Many many.... thanks for your reply.

    I have to do the same taks on both 2000 and 2005 mechines.

    sorry for asking again. I dont want to take my TABLE offline, because my DB is 24/7. Can you suggest me on this. as I dont have much sapce on current drive I would like to move big tables to another drive.

    thanks,

    Kirs

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Are you saying that you have no maintenance windows in which this can be done?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply