I'm taking a backup of a SQL Server 2019 database with CDC enabled, and trying to restore it to a different instance. I don't need CDC on target database so I don't have the "keep_cdc" option in the restore script. Despite this, I'm getting the following error: > Could not upgrade the metadata for database mydbname that is enabled > for Change Data Capture. The failure occurred when executing the > action 'drop function fn_cdc_get_all_changes_'. The > error returned was 3764: 'line 1, state 1, Cannot drop the table > valued function > 'cdc.fn_cdc_get_all_changes_dbo_mytablename' because it is > being used for Change Data Capture.'. Use the action and error to > determine the cause of the failure and resubmit the request. The database seems to restore OK despite the error, but CDC is enabled when I look in sys.databases. When I try to disable cdc on the restored database I get following error: > Could not update the metadata that indicates database mydbname is not enabled for Change Data Capture. The failure occurred when executing the command 'DROP TABLE dbo.systranschemas'. The error returned was 3764: 'Cannot drop the table 'dbo.systranschemas' because it is being used for Change Data Capture.'. Use the action and error to determine the cause of the failure and resubmit the request. Any thoughts as to why this is happening despite the fact that I'm not including keep_CDC in the restore script? Any potential solutions?

How do you correlate SQL Database Audit log column "transaction_id" with a LSN either from the trans log or a CDC capture table? I am trying to do this because cdc only captures the data changes, while SQL Audit captures the who, when and what made the change. I am trying to correlate the two. thx ~j

I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables. Right now, we have triggers on each of the tables of interest, that write the tables PK into a Change_Event table. We can then query that table, and boil it down to the PK that represents the entity as a whole. We can then retrieve the data for that entity and perform the relevant actions upon it. As an example, consider the following (simplified) example: CREATE TABLE Employee ( Id INT IDENTITY PRIMARY KEY, Name VARCHAR(250) NOT NULL, Telephone VARCHAR(15) NOT Null ); CREATE Table Visit ( Id INT IDENTITY PRIMARY KEY, VisitDate DATETIME2 Not NULL, [Address] VARCHAR(500) NOT NULL, VisitorId INT NOT NULL, CONSTRAINT FK_Visit_VisitorId FOREIGN KEY (VisitorId) REFERENCES Employee (Id) ); INSERT into Employee (Name, Telephone) VALUES ('John Doe', '123456789'), ('Jane Smith', '999555333'); INSERT INTO Visit (VisitDate, Address, VisitorId) VALUES (SYSDATETIME(), '123 Fake St', 1), (GETDATE() + 5, '99 Test Av', 2); In this example, the Visit Entity is considered to be the following xml: SELECT Id, CAST(( SELECT Id, VisitDate, Address, ( SELECT E.Id, E.Name, E.Telephone FROM Employee E WHERE E.Id = V.VisitorId FOR XML RAW ('Visitor'), TYPE, ELEMENTS ) Visitor FROM Visit V WHERE V.Id = Visit.Id FOR XML RAW ('Visit'), ELEMENTS ) AS XML) AS Data FROM Visit I need to be able to know, when one of those entities has changed. If I were to change the telephone number of the Employee, I need to be able to see that that has changed the Visit entity so that I can reprocess it. ---- Right now, my change table records the Employee Id. I then run a query that gives me all the visits with that employee as the VisitorId. That sounds fine when you are only looking at two tables, but when you factor in several tables (and potentially several levels of abstraction between them) the performance can get pretty slow. I've looked into Change Data Capture, but that still seems to be capturing at a table level. Equally, I could add Modified date columns to all tables, and combine all the modifieds from the view to produce a single, max modified value - but considering I need to filter on that field, I can't imagine performance would be that great either. Is there a recommended method of dealing with this within SQL Server? As an added consideration - whilst the Instance is SQL 2008, the DB is still in 2000 compatibility mode. **UPDATE** I've been looking into [Change Tracking](https://msdn.microsoft.com/en-us/library/bb964713.aspx) , which works pretty well at a table level. Unfortunately it doesn't work too well with the select above. I even tried creating a schemabound view based on that select, and enabling change tracking on the view, but this appears to be an option that can't apply to views. I can make it work by using something like the below: WITH ChangedVisits AS ( SELECT DISTINCT OV.Id FROM dbo.Visit OV INNER JOIN Employee E ON OV.VisitorId = E.Id LEFT JOIN CHANGETABLE(CHANGES Visit, @LastSyncVersion) AS VCT ON OV.Id = VCT.Id LEFT JOIN CHANGETABLE(CHANGES Employee, @LastSyncVersion) AS ECT ON OV.VisitorId = ECT.Id WHERE VCT.Id IS NOT NULL OR ECT.Id IS NOT NULL ) SELECT Id, CAST( ( SELECT V.Id as [@Id], V.VisitDate, Address, ( SELECT E.Id, E.Name, E.Telephone FROM dbo.Employee E WHERE E.Id = V.VisitorId FOR XML PATH(''), TYPE, ELEMENTS ) Visitor FROM dbo.Visit V WHERE V.Id = CV.Id FOR XML PATH ('Visit'), ELEMENTS ) AS XML) AS Data FROM ChangedVisits CV But the drawback of that is the need to join on each of the tables and an associated CHANGETABLE object in order to work out if something has changed.

On SQL Server 2019, I enabled CDC on some tables about 2 days ago. The CDC cleanup job is scheduled to run every day and the retention period is 3 days. When I checked the job history of the cleanup job, I found nothing there. I ran the job manually and by the next day I found that even the manual job I ran was no longer there. How can I be sure that the cleanup job is running as it should, also could there be anything that is clearing the job history? The database is also part of always-on availability group. Please, help.

I am trying to implement change data capture in SQL Server 2012. I enabled it at database and table level. when i try to update the table, CDC_CT captures the changes. However when i am using SSIS CDC components, it doesnt pick anything. the version of SQL server is: Microsoft SQL Server 2012 (SP3-CU10-GDR) (KB4057121) - 11.0.6615.2 (X64) Jan 9 2018 21:26:48 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) Can someone please guide? More information here: https://stackoverflow.com/questions/51959201/change-data-capture-cdc-ssis-not-working

When the CDC Capture job runs, it fails with this error: > Cannot insert duplicate key row in object cdc.Table_CT' with unique index 'CT Clustered Index. This index is a SQL Server CDC created index on columns __start_lsn, __$command_id, __$seqval, __$operation. Any ideas how to simulate this to trap why this happens, or a way to overcome these issues with CDC? Do I need to enable traceflags, or CU installed, etc? Additional info: RCSI (Read Committed Snapshot Isolation) is enabled on the database. Not sure if this could possibly cause the issue experienced. The only solution to get passed this error was to disable CDC on the database and re-enable the database and then the affected table. In this scenario, we were fortunate to do this, but this can not be done on other environments

I am in the process of migrating our AWS RDS postgresql from one account to another with zero downtime. I am going with pg_dump/pg_restore + AWS CDC for ongoing replication. I am looking for a way to generate and compare checksum to make sure no differences in data or corruption. Q1: Is it possible to generate checksum on the database level rather than table level? Q2: OR doing a select count(*) on all the tables in source/target is better? when there is no downtime, data changes will be coming in the source during this process and i am afraid this will affect the checksum or rowcount comparison. Please advise

I'm implementing an SSIS/CDC based data warehouse solution and have a question relating to referential integrity on the destination database. As an example, I have INVENTORY_TRANSACTION in one table and PART in another table each with their own SSIS package for incremental loads. I don't want to have a situation where I have INVENTORY_TRANSACTIONS in the data warehouse that don't have an associated parent PART record. I've read quite a bit on the subject but I'm still unclear. Should I be using the same capture_instance on the tables that have related data so they all target the same LSN chain? Should I be using the same State name? Or, am I missing something entirely?

There is a CDC enabled table of the following form:

create table dbo.foo (
    id int not null primary key,
    a char(1), 
    b char(1), 
    c char(1), 
    d char(1), 
    e char(1),
    updated datetime not null default getdate(),
    active bit not null default 1
);
go

When querying cdc.dbo_foo_CT, I see the paired records of the following form (_note differences in columns a & updated_): | __$start_lsn | __$end_lsn | __$seqval | __$operation | __$update_mask | id | ⭐a | b | c | d | e | ⭐updated | active | __$command_id | |---|---|---|---|---|---|---|---|---|---|---|---|---|---| | 0x02 | _NULL_ | 0x01 | 1 | 0xFF | 1 | a | b | c | _NULL_ | _NULL_ | 12:00:00 | 1 | 1 | | 0x02 | _NULL_ | 0x01 | 2 | 0xFF | 1 | x | b | c | _NULL_ | _NULL_ | 13:00:00 | 1 | 2 | Given [operations 1 & 2](https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-capture-instance-ct-transact-sql) are delete & insert respectively, I might otherwise expect this to be a full-row delete-then-reinsert pattern from an app; _but_ there are identical values in _both_ __$start_lsn & __$seqval. When attempting to repro a delete/re-insert in a transaction, the __$seqval still increments AFAICT. Notably a merge command produces the delete/re-insert CDC pattern but still increments the __$seqval as expected. CDC was only recently enabled, and _every_ record in cdc.dbo_foo_CT is a paired record of this form although other CDC enabled tables in the same database do not show this pattern. What could be causing this behavior?

I am looking for a way to set up continuous data replication between one Microsoft SQL Server to another Microsoft SQL Server both in AWS. The source tables are updated continuously. The challenge here is that I don't want to enable CDC on the source DB.

I’m running a **wal-consumer** pod in a PostgreSQL environment that streams WAL logs from a **replication slot**. However, I noticed that the order in which I set up my services affects whether the consumer works correctly or lags. Two Different Setup Orders, Two Different Outcomes: **Setup A (Causes Lag)** - Create the replication slot - Run database migrations (creating/modifying tables) - Create the publication **Issue:** The replication slot starts lagging, and the wal-consumer does not consume WAL logs properly—even though no inserts, updates, or deletes are happening. The last confirmed flushed LSN stays where it was initiated. **Setup B (Works as Expected)** - Run database migrations. - Create the publication - Create the replication slot **Outcome:** The wal-consumer streams WAL logs as expected with no lag. **Why Does This Happen?** The replication slot appears to accumulate WAL logs in Setup A, even when no data is changing. But when the publication is created first (as in Setup B), everything works smoothly. **Why does the order of creating the replication slot matter?** Does PostgreSQL handle WAL retention differently depending on whether a publication exists at the time the slot is created? Would love to understand what’s happening under the hood!

My most important production server uses Change Data Capture (CDC) a lot, maybe for about 25 tables. The relevant agent job shows well over 1,000 steps as in progress. Because they are in progress, they do not respect the option in SQL Server Agent's GUI called "Maximum job history rows per job". They do respect the other setting in that same GUI, "Maximum job history log size (in rows)". However, increasing this setting by several thousand has not been enough to cure this problem. How, then, can I make CDC stop capturing a seemingly infinite amount of agent job history? My only idea so far has been to write a custom script to wipe the records of CDC jobs that are currently in progress. Given that the jobs are currently in progress, this seems like a stupid and dangerous idea. I must assume that this is a solved problem. CDC is from [something like 2008](https://techcommunity.microsoft.com/blog/sqlserver/change-data-capture---what-is-it-and-how-do-i-use-it/383694) and SQL Server Agent [is ancient](https://softwareengineering.stackexchange.com/questions/456467/does-sql-server-agent-predate-windows-task-scheduler) . Failure to solve this problem will leave me without agent job history for a lot of my jobs.

Some background. This database was using replication on a previous server version 2012. The replication stopped working. We then upgraded to 2019. I was unable to remove replication objects on the former server prior to the upgrade. After the upgrade the database performs as expected. Now when we enabled CDC and try to execute CDC scan we get SQL dumps in the logs and CDC is obviously not working. I have tried to remove replication from this database, the new server does not have replication set up on it at all. I have tried to give the user running the job permissions to no avail. When I try to run sys.sp_MScdc_capture_job myself under my permissions I get the same result. So I really do not think that this is a permissions issue. I think it has to do with something being left over from the replication on on the previous server. Here is the main thing from the SQL dump that catches my eye: > Access Violation occurred reading address 0000000000000000 Exception > Code = c0000005 EXCEPTION_ACCESS_VIOLATION Exception Address = > 00007FFE953DB45B Module(sqlmin+0000000000D8B45B) When I run sys.sp_MScdc_capture_job manually I get the following error: > Msg 22859, Level 16, State 1, Procedure sp_replcmds, Line 1 [Batch > Start Line 0] Log Scan process failed in processing log records. Refer > to previous errors in the current session to identify the cause and > correct any associated problems. Msg 3621, Level 16, State 6, > Procedure sp_replcmds, Line 1 [Batch Start Line 0] The statement has > been terminated. Msg 22864, Level 16, State 1, Procedure > sys.sp_MScdc_capture_job, Line 102 [Batch Start Line 0] The call to > sp_MScdc_capture_job by the Capture Job for database 'XXXXXXX' failed. > Look at previous errors for the cause of the failure. Any ideas or direction is appreciated. Current version: > Microsoft SQL Server 2019 (RTM-CU29) (KB5046365) - 15.0.4405.4 (X64) > Oct 23 2024 08:45:19 Copyright (C) 2019 Microsoft Corporation > Enterprise Edition: Core-based Licensing (64-bit) on Windows Server > 2022 Datacenter 10.0 (Build 20348: ) (Hypervisor) SQL Dump: 03/18/2025 13:55:12,spid240,Unknown,Dump request is dismissed (stack signature 0x00000001219D5EA8). 03/18/2025 13:55:12,spid240,Unknown,Stack Signature for the dump is 0x00000001219D5EA8 03/18/2025 13:55:12,spid240,Unknown,00007FFEADAFEDCB Module(ntdll+000000000007EDCB) 03/18/2025 13:55:12,spid240,Unknown,00007FFEAC3D4CB0 Module(KERNEL32+0000000000014CB0) 03/18/2025 13:55:12,spid240,Unknown,00007FFE90D241F8 Module(sqldk+00000000000241F8) 03/18/2025 13:55:12,spid240,Unknown,00007FFE90D2440F Module(sqldk+000000000002440F) 03/18/2025 13:55:12,spid240,Unknown,00007FFE90D23532 Module(sqldk+0000000000023532) 03/18/2025 13:55:12,spid240,Unknown,00007FFE90D0A51E Module(sqldk+000000000000A51E) 03/18/2025 13:55:12,spid240,Unknown,00007FFE90D0A95F Module(sqldk+000000000000A95F) 03/18/2025 13:55:12,spid240,Unknown,00007FFE90D09FE3 Module(sqldk+0000000000009FE3) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92003CF3 Module(sqllang+0000000000013CF3) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92003BD6 Module(sqllang+0000000000013BD6) 03/18/2025 13:55:12,spid240,Unknown,00007FFE9200F295 Module(sqllang+000000000001F295) 03/18/2025 13:55:12,spid240,Unknown,00007FFE920060C5 Module(sqllang+00000000000160C5) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92C07BA7 Module(sqllang+0000000000C17BA7) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92C11BC7 Module(sqllang+0000000000C21BC7) 03/18/2025 13:55:12,spid240,Unknown,00007FFE920378E8 Module(sqllang+00000000000478E8) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92037A3F Module(sqllang+0000000000047A3F) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92037C21 Module(sqllang+0000000000047C21) 03/18/2025 13:55:12,spid240,Unknown,00007FFE920060C5 Module(sqllang+00000000000160C5) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92C07BA7 Module(sqllang+0000000000C17BA7) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92C11BC7 Module(sqllang+0000000000C21BC7) 03/18/2025 13:55:12,spid240,Unknown,00007FFE920378E8 Module(sqllang+00000000000478E8) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92037A3F Module(sqllang+0000000000047A3F) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92037C21 Module(sqllang+0000000000047C21) 03/18/2025 13:55:12,spid240,Unknown,00007FFE920060C5 Module(sqllang+00000000000160C5) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92C07BA7 Module(sqllang+0000000000C17BA7) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92C11BC7 Module(sqllang+0000000000C21BC7) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92D50329 Module(sqllang+0000000000D60329) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92D4FF69 Module(sqllang+0000000000D5FF69) 03/18/2025 13:55:12,spid240,Unknown,00007FFE920378E8 Module(sqllang+00000000000478E8) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92037A3F Module(sqllang+0000000000047A3F) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92037C21 Module(sqllang+0000000000047C21) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92C97075 Module(sqllang+0000000000CA7075) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92C8613E Module(sqllang+0000000000C9613E) 03/18/2025 13:55:12,spid240,Unknown,00007FFE92C86EB0 Module(sqllang+0000000000C96EB0) 03/18/2025 13:55:12,spid240,Unknown,00007FFE9539BC52 Module(sqlmin+0000000000D4BC52) 03/18/2025 13:55:12,spid240,Unknown,00007FFE953A03EC Module(sqlmin+0000000000D503EC) 03/18/2025 13:55:12,spid240,Unknown,00007FFE953A1300 Module(sqlmin+0000000000D51300) 03/18/2025 13:55:12,spid240,Unknown,00007FFE953A6A41 Module(sqlmin+0000000000D56A41) 03/18/2025 13:55:12,spid240,Unknown,00007FFE953A7291 Module(sqlmin+0000000000D57291) 03/18/2025 13:55:12,spid240,Unknown,00007FFE953A5A98 Module(sqlmin+0000000000D55A98) 03/18/2025 13:55:12,spid240,Unknown,00007FFE953DDDDC Module(sqlmin+0000000000D8DDDC) 03/18/2025 13:55:12,spid240,Unknown,00007FFE953DDBC6 Module(sqlmin+0000000000D8DBC6) 03/18/2025 13:55:12,spid240,Unknown,00007FFE953DE33F Module(sqlmin+0000000000D8E33F) 03/18/2025 13:55:12,spid240,Unknown,00007FFE953E147B Module(sqlmin+0000000000D9147B) 03/18/2025 13:55:12,spid240,Unknown,00007FFE953DB45B Module(sqlmin+0000000000D8B45B) 03/18/2025 13:55:12,spid240,Unknown,* Short Stack Dump 03/18/2025 13:55:12,spid240,Unknown,* ------------------------------------------------------------------------------- 03/18/2025 13:55:12,spid240,Unknown,* ******************************************************************************* 03/18/2025 13:55:12,spid240,Unknown,* Rip: 00007FFE953DB45B: 52FFC88B48108B48 4C178B4C68468958 8D30458D4C044D8D 92FF41CF8B480153 B8684E8B00000100 48E1F74800000058 03/18/2025 13:55:12,spid240,Unknown,* R15: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* R14: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* R13: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* R12: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* R11: 0FFFFFFFFFFFFFFF: 03/18/2025 13:55:12,spid240,Unknown,* R10: 000002DEDED556C0: 0000000000000000 000002DEDED556C8 000002DEDED556C8 0000000000000000 0000000000000000 000002DE00000000 03/18/2025 13:55:12,spid240,Unknown,* R9: 4000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* R8: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* Rdi: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* Rsi: 000000CEB0874710: 0000000000000000 0000301B53BB000C 000002C9A1288050 FFFFFFFF234C7570 0000000000000000 0000000000000000 03/18/2025 13:55:12,spid240,Unknown,* Rbp: 000000CEB0874560: 0000000000000000 00007FFE00000001 0000000000000000 000002C7EB8767C8 0000000000000000 000002B77CD98AB0 03/18/2025 13:55:12,spid240,Unknown,* Rsp: 000000CEB0874510: 0000000000000000 000000CEB0874710 000000CEB0874710 00007FFE94669E81 000000CE00000000 00007FFE00000001 03/18/2025 13:55:12,spid240,Unknown,* Rbx: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* Rdx: 000000CEB0874390: 000000CEB0874390 000000CEB0874390 000002B77CD98AB0 00007FFE9473C715 0000000000000000 000000CEB0874451 03/18/2025 13:55:12,spid240,Unknown,* Rcx: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* Rax: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* EFlags: 0000000000010206: 03/18/2025 13:55:12,spid240,Unknown,* SegSs: 000000000000002B: 03/18/2025 13:55:12,spid240,Unknown,* SegGs: 000000000000002B: 03/18/2025 13:55:12,spid240,Unknown,* SegFs: 0000000000000053: 03/18/2025 13:55:12,spid240,Unknown,* SegEs: 000000000000002B: 03/18/2025 13:55:12,spid240,Unknown,* SegDs: 000000000000002B: 03/18/2025 13:55:12,spid240,Unknown,* SegCs: 0000000000000033: 03/18/2025 13:55:12,spid240,Unknown,* MxCsr: 0000000000001FA8: 03/18/2025 13:55:12,spid240,Unknown,* ContextFlags: 000000000010005F: 03/18/2025 13:55:12,spid240,Unknown,* P6Home: 000002CC33F81B00: 0000000000000001 0000000000000000 000000000000FFFF 000002B795099EB8 0000000000000000 0000000000000008 03/18/2025 13:55:12,spid240,Unknown,* P5Home: 000002CC33F819F0: 00020000004B0000 0002000C38FB3EA2 0000001200000000 0000000000000000 0000000500000001 000002CC33F81B00 03/18/2025 13:55:12,spid240,Unknown,* P4Home: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* P3Home: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* P2Home: 00007FFE915A1C53: 480000008024848B 909090C35B40C483 9090909090909090 C48B489090909090 C6C9334548EC8348 D948894466E7D840 03/18/2025 13:55:12,spid240,Unknown,* P1Home: 0000000000000000: 03/18/2025 13:55:12,spid240,Unknown,* 03/18/2025 13:55:12,spid240,Unknown,* apphelp 00007FFEA7E30000 00007FFEA7EC0FFF 00091000 03/18/2025 13:55:12,spid240,Unknown,* wldp 00007FFEAA800000 00007FFEAA82DFFF 0002e000 03/18/2025 13:55:12,spid240,Unknown,* Sort00060101 00007FFE8F050000 00007FFE8F062FFF 00013000 03/18/2025 13:55:12,spid240,Unknown,* msxmlsql 00007FFE79290000 00007FFE79464FFF 001d5000 03/18/2025 13:55:12,spid240,Unknown,* SAMLIB 00007FFE9DB30000 00007FFE9DB56FFF 00027000 03/18/2025 13:55:12,spid240,Unknown,* xprepl 00007FFE8FB60000 00007FFE8FB7BFFF 0001c000 03/18/2025 13:55:12,spid240,Unknown,* DSROLE 00007FFEA8A30000 00007FFEA8A39FFF 0000a000 03/18/2025 13:55:12,spid240,Unknown,* sqlvdi 00007FFE8FCB0000 00007FFE8FCEAFFF 0003b000 03/18/2025 13:55:12,spid240,Unknown,* xplog70 00000327D1FE0000 00000327D1FE3FFF 00004000 03/18/2025 13:55:12,spid240,Unknown,* xplog70 00007FFEA8BA0000 00007FFEA8BB5FFF 00016000 03/18/2025 13:55:12,spid240,Unknown,* odbccp32 00007FFE8B7E0000 00007FFE8B806FFF 00027000 03/18/2025 13:55:12,spid240,Unknown,* xpstar 00000327D1A40000 00000327D1A4CFFF 0000d000 03/18/2025 13:55:12,spid240,Unknown,* SQLSCM 00007FFE8BAA0000 00007FFE8BAB3FFF 00014000 03/18/2025 13:55:12,spid240,Unknown,* xpstar 00007FFE8B730000 00007FFE8B7A1FFF 00072000 03/18/2025 13:55:12,spid240,Unknown,* xpsqlbot 00007FFE8B7B0000 00007FFE8B7B9FFF 0000a000 03/18/2025 13:55:12,spid240,Unknown,* ncryptsslp 00007FFE97C70000 00007FFE97C95FFF 00026000 03/18/2025 13:55:12,spid240,Unknown,* windows.storage 00007FFE9F0D0000 00007FFE9F912FFF 00843000 03/18/2025 13:55:12,spid240,Unknown,* SqlAccess 00007FFE8BAC0000 00007FFE8BB35FFF 00076000 03/18/2025 13:55:12,spid240,Unknown,* fwpuclnt 00007FFEA8620000 00007FFEA86A2FFF 00083000 03/18/2025 13:55:12,spid240,Unknown,* rasadhlp 00007FFEA6550000 00007FFEA6559FFF 0000a000 03/18/2025 13:55:12,spid240,Unknown,* MSFTE 0000000049980000 0000000049D2DFFF 003ae000 03/18/2025 13:55:12,spid240,Unknown,* ftimport 0000000060000000 0000000060024FFF 00025000 03/18/2025 13:55:12,spid240,Unknown,* DSPARSE 00007FFEA1270000 00007FFEA127BFFF 0000c000 03/18/2025 13:55:12,spid240,Unknown,* ntdsapi 00007FFE9D650000 00007FFE9D679FFF 0002a000 03/18/2025 13:55:12,spid240,Unknown,* ntmarta 00007FFEAA0A0000 00007FFEAA0D3FFF 00034000 03/18/2025 13:55:12,spid240,Unknown,* mskeyprotect 00007FFE97C20000 00007FFE97C34FFF 00015000 03/18/2025 13:55:12,spid240,Unknown,* profapi 00007FFEAAF10000 00007FFEAAF30FFF 00021000 03/18/2025 13:55:12,spid240,Unknown,* SRVCLI 00007FFEA99E0000 00007FFEA9A07FFF 00028000 03/18/2025 13:55:12,spid240,Unknown,* clrjit 00007FFE8BB40000 00007FFE8BC6EFFF 0012f000 03/18/2025 13:55:12,spid240,Unknown,* BatchParser 00007FFE8D140000 00007FFE8D169FFF 0002a000 03/18/2025 13:55:12,spid240,Unknown,* ucrtbase_clr0400 00007FFE8CD00000 00007FFE8CDCCFFF 000cd000 03/18/2025 13:55:12,spid240,Unknown,* VCRUNTIME140_CLR0400 00007FFE8D170000 00007FFE8D18AFFF 0001b000 03/18/2025 13:55:12,spid240,Unknown,* VCRUNTIME140_1_CLR0400 00007FFE976F0000 00007FFE976FBFFF 0000c000 03/18/2025 13:55:12,spid240,Unknown,* clr 00007FFE8BC70000 00007FFE8C613FFF 009a4000 03/18/2025 13:55:12,spid240,Unknown,* SQLNCLIR11 00000327CDDF0000 00000327CDE27FFF 00038000 03/18/2025 13:55:12,spid240,Unknown,* MSVCR100 000000005B740000 000000005B811FFF 000d2000 03/18/2025 13:55:12,spid240,Unknown,* sqlncli11 00007FFE8C620000 00007FFE8C972FFF 00353000 03/18/2025 13:55:12,spid240,Unknown,* DNSAPI 00007FFEAA250000 00007FFEAA336FFF 000e7000 03/18/2025 13:55:12,spid240,Unknown,* dhcpcsvc6 00007FFEA8410000 00007FFEA8428FFF 00019000 03/18/2025 13:55:12,spid240,Unknown,* NSI 00007FFEABBA0000 00007FFEABBA8FFF 00009000 03/18/2025 13:55:12,spid240,Unknown,* MSOLEDBSQLR 00000327CDD90000 00000327CDDB2FFF 00023000 03/18/2025 13:55:12,spid240,Unknown,* IPHLPAPI 00007FFEAA220000 00007FFEAA24CFFF 0002d000 03/18/2025 13:55:12,spid240,Unknown,* MSWSOCK 00007FFEAA500000 00007FFEAA567FFF 00068000 03/18/2025 13:55:12,spid240,Unknown,* COMCTL32 00007FFE98570000 00007FFE98621FFF 000b2000 03/18/2025 13:55:12,spid240,Unknown,* SHELL32 00007FFEABBB0000 00007FFEAC309FFF 0075a000 03/18/2025 13:55:12,spid240,Unknown,* shcore 00007FFEAC510000 00007FFEAC5F9FFF 000ea000 03/18/2025 13:55:12,spid240,Unknown,* COMDLG32 00007FFEAC600000 00007FFEAC6E2FFF 000e3000 03/18/2025 13:55:12,spid240,Unknown,* msoledbsql 00007FFE8C980000 00007FFE8CC22FFF 002a3000 03/18/2025 13:55:12,spid240,Unknown,* msxml3 00007FFE8CDD0000 00007FFE8D03BFFF 0026c000 03/18/2025 13:55:12,spid240,Unknown,* clbcatq 00007FFEAC310000 00007FFEAC3BEFFF 000af000 03/18/2025 13:55:12,spid240,Unknown,* SqlServerSpatial150 00007FFE8D190000 00007FFE8D231FFF 000a2000 03/18/2025 13:55:12,spid240,Unknown,* mscoreei 00007FFE8D240000 00007FFE8D2DAFFF 0009b000 03/18/2025 13:55:12,spid240,Unknown,* MSCOREE 00007FFE8D2E0000 00007FFE8D346FFF 00067000 03/18/2025 13:55:12,spid240,Unknown,* SECURITY 00000327CD930000 00000327CD932FFF 00003000 03/18/2025 13:55:12,spid240,Unknown,* schannel 00007FFEA9CB0000 00007FFEA9D52FFF 000a3000 03/18/2025 13:55:12,spid240,Unknown,* kerberos 00007FFEAA630000 00007FFEAA74EFFF 0011f000 03/18/2025 13:55:12,spid240,Unknown,* cryptdll 00007FFEAA570000 00007FFEAA584FFF 00015000 03/18/2025 13:55:12,spid240,Unknown,* NtlmShared 00007FFEAA450000 00007FFEAA45DFFF 0000e000 03/18/2025 13:55:12,spid240,Unknown,* msv1_0 00007FFEAA460000 00007FFEAA4F2FFF 00093000 03/18/2025 13:55:12,spid240,Unknown,* ncryptprov 00007FFEA24D0000 00007FFEA2545FFF 00076000 03/18/2025 13:55:12,spid240,Unknown,* XmlLite 00007FFEA4890000 00007FFEA48C6FFF 00037000 03/18/2025 13:55:12,spid240,Unknown,* SHLWAPI 00007FFEAD600000 00007FFEAD65EFFF 0005f000 03/18/2025 13:55:12,spid240,Unknown,* hkcompile 00007FFE8D350000 00007FFE8D48FFFF 00140000 03/18/2025 13:55:12,spid240,Unknown,* hkengine 00007FFE8D490000 00007FFE8DB87FFF 006f8000 03/18/2025 13:55:12,spid240,Unknown,* dbghelp 00007FFE9E170000 00007FFE9E380FFF 00211000 03/18/2025 13:55:12,spid240,Unknown,* hkruntime 00007FFE8DB90000 00007FFE8DE62FFF 002d3000 03/18/2025 13:55:12,spid240,Unknown,* VERSION 00007FFEA41A0000 00007FFEA41A9FFF 0000a000 03/18/2025 13:55:12,spid240,Unknown,* RESUTILS 00007FFE9B730000 00007FFE9B7C8FFF 00099000 03/18/2025 13:55:12,spid240,Unknown,* CLUSAPI 00007FFE9B5F0000 00007FFE9B728FFF 00139000 03/18/2025 13:55:12,spid240,Unknown,* sqlevn70 000002B6F3130000 000002B6F3470FFF 00341000 03/18/2025 13:55:12,spid240,Unknown,* cscapi 00007FFE98D80000 00007FFE98D91FFF 00012000 03/18/2025 13:55:12,spid240,Unknown,* wkscli 00007FFEA5EA0000 00007FFEA5EB9FFF 0001a000 03/18/2025 13:55:12,spid240,Unknown,* gpapi 00007FFEAA590000 00007FFEAA5B4FFF 00025000 03/18/2025 13:55:12,spid240,Unknown,* imagehlp 00007FFEAD6C0000 00007FFEAD6DEFFF 0001f000 03/18/2025 13:55:12,spid240,Unknown,* CRYPTBASE 00007FFEAA770000 00007FFEAA77BFFF 0000c000 03/18/2025 13:55:12,spid240,Unknown,* rsaenh 00007FFEA9DC0000 00007FFEA9DF4FFF 00035000 03/18/2025 13:55:12,spid240,Unknown,* CRYPTSP 00007FFEAA750000 00007FFEAA76AFFF 0001b000 03/18/2025 13:55:12,spid240,Unknown,* instapi150 00007FFE989C0000 00007FFE989D3FFF 00014000 03/18/2025 13:55:12,spid240,Unknown,* bcryptPrimitives 00007FFEAB170000 00007FFEAB1EEFFF 0007f000 03/18/2025 13:55:12,spid240,Unknown,* kernel.appcore 00007FFEA9E60000 00007FFEA9E76FFF 00017000 03/18/2025 13:55:12,spid240,Unknown,* MSASN1 00007FFEAA990000 00007FFEAA9A1FFF 00012000 03/18/2025 13:55:12,spid240,Unknown,* psapi 00007FFEAD270000 00007FFEAD277FFF 00008000 03/18/2025 13:55:12,spid240,Unknown,* NTASN1 00007FFEAA830000 00007FFEAA866FFF 00037000 03/18/2025 13:55:12,spid240,Unknown,* LOGONCLI 00007FFEA8820000 00007FFEA8862FFF 00043000 03/18/2025 13:55:12,spid240,Unknown,* SAMCLI 00007FFEA83B0000 00007FFEA83C8FFF 00019000 03/18/2025 13:55:12,spid240,Unknown,* SSPICLI 00007FFEAA9B0000 00007FFEAA9F9FFF 0004a000 03/18/2025 13:55:12,spid240,Unknown,* NETUTILS 00007FFEAA340000 00007FFEAA34BFFF 0000c000 03/18/2025 13:55:12,spid240,Unknown,* dhcpcsvc 00007FFEA83F0000 00007FFEA840CFFF 0001d000 03/18/2025 13:55:12,spid240,Unknown,* AUTHZ 00007FFEA9B50000 00007FFEA9B9DFFF 0004e000 03/18/2025 13:55:12,spid240,Unknown,* USERENV 00007FFEAA600000 00007FFEAA62DFFF 0002e000 03/18/2025 13:55:12,spid240,Unknown,* DPAPI 00007FFEAAC70000 00007FFEAAC79FFF 0000a000 03/18/2025 13:55:12,spid240,Unknown,* ncrypt 00007FFEAA870000 00007FFEAA89EFFF 0002f000 03/18/2025 13:55:12,spid240,Unknown,* WINTRUST 00007FFEAB100000 00007FFEAB16AFFF 0006b000 03/18/2025 13:55:12,spid240,Unknown,* secforwarder 00007FFE97700000 00007FFE97710FFF 00011000 03/18/2025 13:55:12,spid240,Unknown,* sqllang 00007FFE91FF0000 00007FFE94645FFF 02656000 03/18/2025 13:55:12,spid240,Unknown,* ODBC32 00007FFE98C60000 00007FFE98D18FFF 000b9000 03/18/2025 13:55:12,spid240,Unknown,* WINHTTP 00007FFEA9E80000 00007FFEA9F87FFF 00108000 03/18/2025 13:55:12,spid240,Unknown,* Secur32 00007FFE9EC10000 00007FFE9EC1BFFF 0000c000 03/18/2025 13:55:12,spid240,Unknown,* WININET 00007FFE90650000 00007FFE90B4DFFF 004fe000 03/18/2025 13:55:12,spid240,Unknown,* VCRUNTIME140_1 00007FFE9EC00000 00007FFE9EC0BFFF 0000c000 03/18/2025 13:55:12,spid240,Unknown,* WS2_32 00007FFEAD9C0000 00007FFEADA30FFF 00071000 03/18/2025 13:55:12,spid240,Unknown,* WINMM 00007FFE9EEA0000 00007FFE9EEC6FFF 00027000 03/18/2025 13:55:12,spid240,Unknown,* VCRUNTIME140 00007FFE9FC50000 00007FFE9FC6DFFF 0001e000 03/18/2025 13:55:12,spid240,Unknown,* MPR 00007FFE9BE30000 00007FFE9BE4CFFF 0001d000 03/18/2025 13:55:12,spid240,Unknown,* MSVCP140 00007FFE9EED0000 00007FFE9EF5CFFF 0008d000 03/18/2025 13:55:12,spid240,Unknown,* sqlmin 00007FFE94650000 00007FFE976CFFFF 03080000 03/18/2025 13:55:12,spid240,Unknown,* qds 00007FFE90BD0000 00007FFE90CFCFFF 0012d000 03/18/2025 13:55:12,spid240,Unknown,* svl 00007FFE98930000 00007FFE9895CFFF 0002d000 03/18/2025 13:55:12,spid240,Unknown,* opends60 00007FFE986C0000 00007FFE986C8FFF 00009000 03/18/2025 13:55:12,spid240,Unknown,* OLEAUT32 00007FFEAB990000 00007FFEABA66FFF 000d7000 03/18/2025 13:55:12,spid240,Unknown,* sqldk 00007FFE90D00000 00007FFE91233FFF 00534000 03/18/2025 13:55:12,spid240,Unknown,* sqlTsEs 00007FFE915A0000 00007FFE91E6CFFF 008cd000 03/18/2025 13:55:12,spid240,Unknown,* pdh 00007FFE98960000 00007FFE989AFFFF 00050000 03/18/2025 13:55:12,spid240,Unknown,* SQLOS 00007FFE989B0000 00007FFE989B7FFF 00008000 03/18/2025 13:55:12,spid240,Unknown,* NETAPI32 00007FFE9EC20000 00007FFE9EC38FFF 00019000 03/18/2025 13:55:12,spid240,Unknown,* combase 00007FFEAD280000 00007FFEAD5F0FFF 00371000 03/18/2025 13:55:12,spid240,Unknown,* USER32 00007FFEACF30000 00007FFEAD0D4FFF 001a5000 03/18/2025 13:55:12,spid240,Unknown,* gdi32full 00007FFEAAFE0000 00007FFEAB0FAFFF 0011b000 03/18/2025 13:55:12,spid240,Unknown,* win32u 00007FFEAB960000 00007FFEAB985FFF 00026000 03/18/2025 13:55:12,spid240,Unknown,* GDI32 00007FFEACF00000 00007FFEACF2BFFF 0002c000 03/18/2025 13:55:12,spid240,Unknown,* msvcp_win 00007FFEAB8C0000 00007FFEAB95FFFF 000a0000 03/18/2025 13:55:12,spid240,Unknown,* ole32 00007FFEACCE0000 00007FFEACE15FFF 00136000 03/18/2025 13:55:12,spid240,Unknown,* RPCRT4 00007FFEAD6E0000 00007FFEAD7FDFFF 0011e000 03/18/2025 13:55:12,spid240,Unknown,* bcrypt 00007FFEAB620000 00007FFEAB646FFF 00027000 03/18/2025 13:55:12,spid240,Unknown,* sechost 00007FFEAC6F0000 00007FFEAC791FFF 000a2000 03/18/2025 13:55:12,spid240,Unknown,* msvcrt 00007FFEABAF0000 00007FFEABB92FFF 000a3000 03/18/2025 13:55:12,spid240,Unknown,* ADVAPI32 00007FFEACE20000 00007FFEACED0FFF 000b1000 03/18/2025 13:55:12,spid240,Unknown,* ucrtbase 00007FFEAB7B0000 00007FFEAB8BFFFF 00110000 03/18/2025 13:55:12,spid240,Unknown,* CRYPT32 00007FFEAB650000 00007FFEAB7AEFFF 0015f000 03/18/2025 13:55:12,spid240,Unknown,* KERNELBASE 00007FFEAB2B0000 00007FFEAB61FFFF 00370000 03/18/2025 13:55:12,spid240,Unknown,* KERNEL32 00007FFEAC3C0000 00007FFEAC47DFFF 000be000 03/18/2025 13:55:12,spid240,Unknown,* ntdll 00007FFEADA80000 00007FFEADC80FFF 00201000 03/18/2025 13:55:12,spid240,Unknown,* sqlservr 00007FF7EADE0000 00007FF7EAE7EFFF 0009f000 03/18/2025 13:55:12,spid240,Unknown,* MODULE BASE END SIZE 03/18/2025 13:55:12,spid240,Unknown,* 03/18/2025 13:55:12,spid240,Unknown,* 03/18/2025 13:55:12,spid240,Unknown,* sys.sp_MScdc_capture_job 03/18/2025 13:55:12,spid240,Unknown,* Input Buffer 70 bytes - 03/18/2025 13:55:12,spid240,Unknown,* Access Violation occurred reading address 0000000000000000 03/18/2025 13:55:12,spid240,Unknown,* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION 03/18/2025 13:55:12,spid240,Unknown,* Exception Address = 00007FFE953DB45B Module(sqlmin+0000000000D8B45B) 03/18/2025 13:55:12,spid240,Unknown,* 03/18/2025 13:55:12,spid240,Unknown,* 03/18/2025 13:55:12,spid240,Unknown,* 03/18/25 13:55:12 spid 240 03/18/2025 13:55:12,spid240,Unknown,* BEGIN STACK DUMP: 03/18/2025 13:55:12,spid240,Unknown,* 03/18/2025 13:55:12,spid240,Unknown,* ******************************************************************************* 03/18/2025 13:55:12,spid240,Unknown,SqlDumpExceptionHandler: Process 240 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 03/18/2025 13:55:12,spid240,Unknown,***Stack Dump being sent to E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\SQLDump0045.txt

I have set up a Change Data Capture (CDC) pipeline using PeerDB to mirror tables from a PostgreSQL standby read replica to ClickHouse. • The PostgreSQL database contains terabytes of data. • The initial snapshot of the existing data needs to be loaded into ClickHouse. • PeerDB is configured to pull from the standby read replica. Questions: 1. How long will the initial snapshot take? Are there any benchmarks or estimations based on database size? 2. Will the initial snapshot affect the standby PostgreSQL server’s performance? • Since it is a read replica, will PeerDB’s snapshot queries (e.g., COPY, SELECT * FROM) put significant load on it? • Would it impact replication lag from the primary database? 3. Are there any best practices to optimize the initial snapshot process to minimize impact on the standby server?

The company I work for put everything on Azure. We use SQL Server replication to move data from one big collection db server (Managed Instance) to our other database servers (say 20 in total). Every day we publish around 10 million (some days will be more, say 100 million plus) new/updated data in various databases. We can only have one publisher (my understanding), and fairly often, we see replication commands get built up, things slow down, and our DBAs will firefight to get things moving. In the database server where all the data is collected, we enabled CDC change tracking. I am wondering if I should create 10 or 20 Azure functions (C# code) to periodically pull changes from CDC. These Azure functions will then copy the changes to our 20 database servers (say all these servers need all these data). Would this be a reasonable alternative to replication? For me, each Azure function acts like a distributor, so we suddenly will have 10 or 20 distributors other than just one. I could use Azure Data Factory to do it, but it is way too expensive compared to Azure functions in my case. Is this a good idea or would we have any big issues?

I have change data capture enabled for most of the tables in one of my databases. Database is hosted on SQL Server instance with version 2016. I have some testing to be done, so I wanted to restore backup of that DB in my test environment. But I am really struggling with that (I am not sure what I am doing wrong). Firstly I tried to restore that as any other DB. The restore is working fine... till the very end when is returning the error that it cannot drop CDC tables because it's not enabled. So I added keep_cdc keyword to restore command and it finished successfully. As I do not need CDC at test I wanted to disable it... but it's in a weird state: exec sys.sp_cdc_disable_db returns error: >Could not update the metadata that indicates database DB is not enabled for Change Data Capture. The failure occurred when executing the command 'drop user cdc'. The error returned was 15284: 'The database principal has granted or denied permissions to objects in the database and cannot be dropped.'. Use the action and error to determine the cause of the failure and resubmit the request. So... error says that CDC is disabled.. but when I run sys.sp_cdc_enable_db I got an error: >Database 'DB' is already enabled for Change Data Capture. Ensure that the correct database context is set, and retry the operation. To report on the databases enabled for Change Data Capture, query the is_cdc_enabled column in the sys.databases catalog view. And is_cdc_enabled indeed shows 1 for that DB. So I wanted to to use GUI. It also shows that cdc is enabled: [![enter image description here]] But, when I change that to 'False' after ok I am getting error: >Change tracking is enabled for one or more tables in database 'DB'. Disable change tracking on each table before disabling it for the database. Use the sys.change_tracking_tables catalog view to obtain a list of tables for which change tracking is enabled. And now the tricky part that I do not understand at all. select * from sys.change_tracking_tables returns 30 tables. While sys.tables I had > 100 tables with is_tracked_by_cdc not equal to 0. I executed sys.sp_cdc_disable_table procedure for each such table `select * from sys.tables where is_tracked_by_cdc 0 and as a result all these tables have is_tracked_by_cdc equal 0 in sys.tables... but sys.change_tracking_tables still returns 30 tables and running sys.sp_cdc_disable_table against them does not change anything. As a result I cannot disable that feature on database level as well. Could you please advise how can I get rid of CDC completely at such copy? (I was trying dropping all CDC object as well but without success)

Today I re-enabled CDC on the primary node of an availability group. When the jobs were created the cleanup job failed with the following error: > Could not delete change table entries made obsolete by a change in one or more low water marks for capture instances of database DB1. The failure occurred when executing the command 'delete top( @p1 ) from [cdc].[dbo_Table1_CT] where __$start_lsn Disable: > >Change data capture instance 'dbo_Table1' has not been enabled for the source table 'dbo.Table1'. >Enable: > >Could not create a capture instance because the capture instance name 'dbo_Table1' already exists in the current database. I'm at a loss on how to get this either removed from CDC or updated so the NULLs don't appear.

I have an SQL Server Database with 100's of tables with as many rows in each and I'm turning on CDC for some reporting. Since data was created/inserted BEFORE turning on CDC, the CDC tables are current empty and I would like to get the data into the CDC tables to start reporting. Is there a way to 'refresh' or seed the existing data into the CDC tables? ------ I've tried a simple update with existing data, however this doesn't actually cause a CDC entry. Presumably it isn't writing to the transaction log and triggering an write.

UPDATE table1 SET column1 = column1;

In my case, most of my tables currently have an UPDATEDON column which I could just update to a now timestamp, which will cause an update and subsequent entries in the CDC tables, but I'd prefer to retain the existing update time.

UPDATE table1 set UPDATEDON = getDateUtc();

Alternatively I could go through and move all the data into temporary tables, delete the data and re-insert it to cause updates, but surely there is a better way? How have others tackled this?

Something I can't understand this behavior and it seems to me bug in SQL Server: I have enabled CDC for table Editorial_schedule_index_cards, now when I update a record from this table with this way:

update esic set target_name = 'test8'
from Editorial_Schedule_Index_Cards esic
inner join editorial_schedule es on es.Index_Card_ID = esic.Index_Card_ID 
where es.last_updated_by = 'IMG92'

and then query the CDC table as below so the output is no record, which is normal, specially target_name is not part of captured columns

select *
from cdc.dbo_editorial_schedule_index_cards_ct
where index_card_id =709339

but when I do the update as the following by using variable in the where clause and point to the same value:

declare @LastUpdatedBy varchar (50)
set @LastUpdatedBy= 'IMG92'
update esic set target_name = 'test8'
from Editorial_Schedule_Index_Cards esic
inner join editorial_schedule es on es.Index_Card_ID = esic.Index_Card_ID 
where es.last_updated_by = @LastUpdatedBy

which is basically I put 'IMG92' into variable, and then query as below

select *
from cdc.dbo_editorial_schedule_index_cards_ct
where index_card_id = 709339

so I got two records, one as Delete and the other as Add - why? It seems the record is deleted and recreated?

Is there any issue you have faced enabling table partitioning for CDC enabled tables? I have done some quick testing and table partitioning for CDC enabled tables is working well in terms of switching data. I understand, I cannot use truncate on the table or a partition while CDC is enabled:

TRUNCATE TABLE schema.tablename WITH (PARTITIONS (22));

-none
Msg 4711, Level 16, State 1, Line 96
Cannot truncate table because it is published for replication 
or enabled for Change Data Capture.

I am not sure, is it ok to disable CDC before the truncate and then enable it again? Or will that cause issues for existing CDC data capture? I believe I can back up the CDC table before disabling, do the truncate and then enable it again. Since truncate partition is done once monthly. I would like to get experienced people's suggestions before implementing in production.