February 11, 2013 at 12:23 pm
Hi All,
I understand that when we enable CDC on a database, all the respective CT Tables are created on the same array as base tables. can we possibly move these CT Tables to different disk?
How is it possible? Can some one please tell me.?
February 11, 2013 at 12:29 pm
I just read online that While enabling CDC, we can point the CT table to reside on different filegroup. Now my question is is there a way to move the CT table without loosing the existing History.
Something like backup and restore with Keep_CDC Option and point the CT tables to different file group..?
I might be completely wrong....
February 14, 2013 at 1:59 am
I'm sure you already foun this script
DECLARE @schema_name VARCHAR(50)
DECLARE @table_name VARCHAR(100)
DECLARE @sql VARCHAR(500)
DECLARE @new_filegroup VARCHAR(100)
SET @schema_name = 'Person'
SET @table_name = 'Contact'
SET @new_filegroup = 'AW_CDC'
IF NOT EXISTS (SELECT * FROM sys.filegroups F WHERE name = @new_filegroup)
BEGIN
PRINT 'Invalid Filegroup'
RETURN
end
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[cdc].['+@schema_name+'_'+@table_name+'_CT]') AND name = @schema_name+'_'+@table_name+'_CT_clustered_idx')
BEGIN
PRINT 'No CDC table for specified schema and table name'
RETURN
END
ELSE
begin
SET @sql = 'DROP INDEX ['+@schema_name+'_'+@table_name+'_CT_clustered_idx] ON [cdc].['+@schema_name+'_'+@table_name+'_CT] WITH ( ONLINE = OFF )'
EXEC (@sql)
SET @sql = '
CREATE UNIQUE CLUSTERED INDEX ['+@schema_name+'_'+@table_name+'_CT_clustered_idx] ON [cdc].['+@schema_name+'_'+@table_name+'_CT]
(
[__$start_lsn] ASC,
[__$seqval] ASC,
[__$operation] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON '+@new_filegroup
EXEC (@sql)
end
You could maybe copy the existing data to a temporary table and then copy it back.
Take a look at this link!
Now you just have to do the transfer :P...
Greetz
Query Shepherd
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply