First post, so I'll get right to it. Thank you in advance for your answers and consideration.
I have full privileges on the database engine that the DB in question is running on, including sysadmin.
To the best of my knowledge, I have enabled this correctly according to documentation, doing the following:
Running the command EXEC sys.sp_cdc_enable_db via a c# application
that I am using as an interface to deal with setting up, recording,
and comparing DML database changes.
From the same application, running the command
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'ORD_ATTACHMENTS',
@role_name = NULL
I have verified that the DB in question is ready for CDC using SELECT [name], database_id, is_cdc_enabled FROM sys.databases.
The table's readiness I have also verified using SELECT [name], is_tracked_by_cdc FROM sys.tables.
Running SELECT * FROM [msdb].[dbo].[cdc_jobs] WHERE [database_id] = DB_ID() in the database context yields the following information for the capture job:
maxtrans: 7200 maxscans: 10 continuous: 1 pollinginterval: 5
retention and threshold are 0.
After inserting records into the table in question via SSMS, the related CDC table, though present, does not have any data in it. No errors were encountered, and the record was successfully added to the source table.
Additional information:
The source table has the following fields:
[AttachmentID] [bigint] IDENTITY(1,1) NOT NULL,
[ORDNUM] [nvarchar](10) NOT NULL,
[FileName] [nvarchar](260) NOT NULL,
[FileContent] [varbinary](max) NULL,
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_ORD_ATTACHMENTS_CreatedOn] DEFAULT (getdate())
No fields are excluded from CDC for this table.
Thank you in advance for any assistance.
Best regards, Chris.
Update 2016-09-20 15:15: Ran the following:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
Have now switched to a test DB to simplify matters. Re-enabled the CDC on my new test table (fields are bigint PK identity field and an NVARCHAR(50) nullable field). Still not working. Also, the capture job has no history entries under SQL Server Agent.
Update 2016-09-20 20:09 Ran sp_MScdc_capture_job in the DB context. This can be, depending on job settings, a continuously executing procedure. Data was found in the CDC table upon running this. Will try to figure out how to automatically engage this.
Update 2016-09-28 17:19 The capture job is scripted as follows:
USE [msdb]
GO
/****** Object: Job [cdc.CDCTest_capture] Script Date: 2016-09-28 5:18:13 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [REPL-LogReader] Script Date: 2016-09-28 5:18:13 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-LogReader' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-LogReader'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'cdc.CDCTest_capture',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'CDC Log Scan Job',
@category_name=N'REPL-LogReader',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Starting Change Data Capture Collection Agent] Script Date: 2016-09-28 5:18:14 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Starting Change Data Capture Collection Agent',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=10,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'RAISERROR(22801, 10, -1)',
@server=N'AECON-SQL',
@database_name=N'CDCTest',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Change Data Capture Collection Agent] Script Date: 2016-09-28 5:18:14 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Change Data Capture Collection Agent',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=10,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'sys.sp_MScdc_capture_job',
@server=N'AECON-SQL',
@database_name=N'CDCTest',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'CDC capture agent schedule.',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160920,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'd1fc7d85-c051-4b24-af84-5505308caaf0'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
The problem turned out to be that the SQL Server Agent job was not running, although SQL Server indicated that it was. Turned this service on in the services console and was able to capture data in CDC.
Special thanks to LogicalMan who very patiently worked with me through all of this.