April 26, 2010 at 8:07 am
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
April 26, 2010 at 8:16 am
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
April 26, 2010 at 8:20 am
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!!
April 26, 2010 at 11:29 am
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
April 26, 2010 at 11:34 am
Are you saying that you have no maintenance windows in which this can be done?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply