1

I have just restored my database using the below script,

USE [master]
GO
RESTORE DATABASE [MyDB] 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLSTANDARD\MSSQL\Backup\mydb.bak' WITH  FILE = 1, 
NOUNLOAD,  REPLACE,  STATS = 5, KEEP_CDC
GO


USE MyDB
GO
EXECUTE sys.sp_cdc_add_job 'capture';
GO
EXECUTE sys.sp_cdc_add_job 'cleanup';
GO

Then I checked the CDC,

select is_tracked_by_cdc,name from sys.tables order by is_tracked_by_cdc desc
select is_cdc_enabled,name from sys.databases order by is_cdc_enabled desc

CDC enabled for my tables. Then I ran UPDATE script,

UPDATE MyTable SET Name='SomeName' where id=1

But found no record in cdc table,

SELECT * FROM [cdc].[dbo_MyTable_CT]

I have installed the latest Cumulative Update (CU31).

1 Answer 1

1

Make sure that jobs exist in SQL Agent for CDC capture and cleanup for your database.
They would look like this:

cdc.MyDB_capture   
cdc.MyDB_cleanup

And they have to be enabled, of course

Also, try disabling and re-enabling database and table for CDC, using commands from this link:

https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-ver16

8
  • I see these jobs cdc.MyDB_capture and cdc.MyDB_cleanup jobs available Oct 5, 2023 at 10:37
  • @ImranQadirBaksh-Baloch updated answer, try re-enabling cdc Oct 5, 2023 at 10:40
  • I ran RAISERROR(22801, 10, -1); sys.sp_MScdc_capture_job manaully and found this message Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. Oct 5, 2023 at 10:41
  • When I ran disable command I am getting Could not update the metadata that indicates database MyDB is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_MScdc_ddl_database triggers] 'drop''. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request. Oct 5, 2023 at 10:44
  • try using the account with "sysadmin" privilege (sa, for example) Oct 5, 2023 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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