Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am facing problem with CDC tables in my database. I think few of these tables are damaged and are not capturing any audit information. I tried to disable CDC on these tables, but that did not work (I didn't even get an error). So I manually dropped a table.

Now I cannot see the table in system tables - but its still not properly deleted. So I cannot re-enable it.

I have got a fix here: http://support.microsoft.com/kb/966306

which is to manually create the CDC table of the same name in system tables - and retry disabling it.

Now the question is how do I manually create a system table.

Thanks

share|improve this question
add comment

2 Answers 2

  1. assume your database name is : DB1.
  2. assume Your last backup of DB1 which cdc tables are exists is : DBbk.
  3. restore DBbk.You can script those objects from this database in which you have successfully enabled CDC.
  4. authorize cdc user in DB1.
  5. mark created cdc tables as system tables in DB1.
  6. fill DB1 cdc tables from DBbk.
  7. do this proccess for all cdc tables which are exists in DBbk and are not exists in DB1.
  8. create a needed index on cdc.change_table.
  9. in security section , change cdc user , db role to 'db_owner'
  10. in security section , correct schema owner to its owner not to cdc
  11. now you can disable cdc and enable it again.

.

/* scripts step 1..6 */


use DB1
GO

/****** Object:  Table [cdc].[captured_columns]    Script Date: 02/11/2013 18:12:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [cdc].[captured_columns](
    [object_id] [int] NOT NULL,
    [column_name] [sysname] NOT NULL,
    [column_id] [int] NULL,
    [column_type] [sysname] NOT NULL,
    [column_ordinal] [int] NOT NULL,
    [is_computed] [bit] NULL,
 CONSTRAINT [captured_columns_clustered_idx] PRIMARY KEY CLUSTERED 
(
    [object_id] ASC,
    [column_ordinal] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

----------------------------
alter authorization on schema::[cdc] to [cdc]

----------------------------
exec sp_MS_marksystemobject '[cdc].[captured_columns]';

---------------------------
insert into [cdc].[captured_columns]
SELECT * FROM [DBbk].[cdc].[captured_columns];

/* scripts step 8 */
/****** Object:  Index [source_object_id_idx]    Script Date: 02/11/2013 18:44:00 ******/
CREATE NONCLUSTERED INDEX [source_object_id_idx] ON [cdc].[change_tables] 
(
    [source_object_id] 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 [PRIMARY]
GO
share|improve this answer
add comment

As the very article you linked says:

You can script those objects from a new dummy database in which you have successfully enabled CDC

These tables are not 'system' as in 'they belong to the sys namespace and protected from direct user operations' but are 'system' as in 'they are absolutely ordinary tables used by stored procedures shipped with CDC and MS just wishes nobody would mess with them'...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.