1

I was testing out the SQL Server 2008 change data capture feature on a table when it became stuck.

  • There is only one table enabled for CDC.
  • After performing various DDL changes to the table to test how CDC responds, I dropped the table.
  • Previously in my testing, dropping the table also causes CDC to drop the CT table, and any meta data related to the dropped table.
  • The last drop statement appears to never have been detected by CDC, as the CT table still exists, and a record exists for it in the cdc.change_tables table
  • I can't disable CDC for the table, or enable CDC for a newly created table with the same name, because the cdc.change_table record exists and is linked to a non-existent object ID in the SQL Server meta data.
  • I tried disabling CDC for the database, but the command ran for 1 hour without response before I stopped it.
  • While attempting to disable CDC for the database, the jobs listing for SQL Server Agent became unresponsive (lock request timeout) in management studio. After canceling the disable CDC command, Agent was still unresponsive.
  • I tried restarting the Agent service. It stopped successfully but hung during (what appeared to be the final stages of) startup. The service is now stuck in "Starting" status.

I'm rebooting the server to attempt to get Agent running again.

I can drop and recreate my database since it's still under development and doesn't have any data in it, but is there any other way to correct the problem? Everything I've read so far indicates that I shouldn't mess directly with any of the CDC resources. Is there any real reason I shouldn't just delete the cdc.change_tables record and related CT table?

Has anyone experienced any similar problems with CDC that would indicate instability with the system?

EDIT: After getting Agent running again, I tried manually deleting the change_tables record and CT table, but when I tried to enable the table, there were a series of errors relating to CDC metadata. However, I was able to disable CDC for the entire database successfully, and then re-enable it. This of course dropped any and all resources related to the CDC feature.

ulty4life
  • 2,972
  • 1
  • 25
  • 31

2 Answers2

2

After getting Agent running again (server reboot), I tried manually deleting the change_tables record and CT table, but when I tried to enable the table, there were a series of errors relating to CDC metadata. However, I was able to disable CDC for the entire database successfully, and then re-enable it. This of course dropped any and all resources related to the CDC feature.

I'm chalking up the weird behavior to the all of the rapid DDL I was running for testing. As this was a test environment, I had options that wouldn't be available in a production environment, at least without backing up the captured data.

I'm concluding that yes, you really shouldn't manually mess with any of the CDC resources.

ulty4life
  • 2,972
  • 1
  • 25
  • 31
  • +1 "you really shouldn't manually mess with any of the CDC resources." My experience has been that, if I get to the stage where fiddling with the CDC-system tables is required, then I've done something wrong. In my case my mistake was to remove the CDC user (added automatically when enabling Change Data Capture on a database) from the db_owner role. This then stopped the cleanup job from functioning and prevented me from disabling CDC on the DB. – Dan Nolan Nov 09 '11 at 04:46
1

I found that trying to run EXEC sys.sp_cdc_disable_db fails in a database not owned by sa.

(I still see the cdc tables by select db_name() as dbname , p.rows,* from sys.objects o join sys.partitions p on o.object_id=p.object_id where o.schema_id=schema_id('cdc') and p.index_id < 2)

Sys.sp_cdc_disable_db immediately succeeds after simply exec sp_changedbowner 'sa'

khr055
  • 28,690
  • 16
  • 36
  • 48
Jack Ellison
  • 11
  • 1
  • 2
    Does it specifically need to be owned by sa, or merely have the correct permissions on it? – ulty4life Sep 11 '12 at 22:53
  • Over the past several months, I've become much more familiar with the inner workings of the CDC replication, scanning jobs, and stored procedures related to the log scanning process(es) and monitoring thereof. There are some additional tricks to stopping jobs via cdc SPs that free up replication blocking status. – ulty4life Sep 11 '12 at 22:56
  • My two cents: in my case `sa` user was disabled and the new owner (for example, domain user as in my case) of fixed database was specified via user interface in SSMS i have got an error like that `could not update the metadata that indicates database 'MyDatabase' is enabled for change data capture` when i have tried to enable `CDC`. My solution is change fixed to CDC database owner via regular T-SQL: `exec sp_changedbowner 'domain\user'` then i run `exec sys.sp_cdc_enable_db` and everything work fine – Konstantin Apr 30 '13 at 11:04