Događaj
31. ožu 23 - 2. tra 23
Najveći događaj učenja SQL-a, Fabrica i Power BI-ja. 31. ožujka – 2. travnja. Upotrijebite kod FABINSIDER da uštedite 400 USD.
Registrirajte se već danasKnown limitations, issues, and errors with CDC
Applies to: 
SQL Server
Azure SQL Managed Instance
This article explains known limitations, issues, and errors with change data capture (CDC) for SQL Server and Azure SQL Managed Instance.
For Azure SQL Database, see Known issues with CDC in Azure SQL Database.
For CDC to function properly, you shouldn't manually modify any CDC metadata such as CDC schema, change tables, CDC system stored procedures, default cdc user permissions (sys.database_principals) or rename the cdc user.
Any objects in sys.objects with is_ms_shipped property set to 1 shouldn't be modified.
SQL
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,is_ms_shipped
FROM sys.objects
WHERE is_ms_shipped= 1 AND SCHEMA_NAME(schema_id) = 'cdc'
It's important to be aware of a situation where you have different collations between the database and the columns of a table configured for change data capture. CDC uses interim storage to populate side tables. If a table has char or varchar columns with collations that are different from the database collation, and if those columns store non-ASCII characters (such as double byte DBCS characters), CDC might not be able to persist the changed data consistent with the data in the base tables. This is because the interim storage variables can't have collations associated with them.
Consider one of the following approaches to ensure captured change data is consistent with base tables:
Use nchar or nvarchar data type for columns containing non-ASCII data.
Or, Use the same collation for columns and for the database.
For example, if you have one database that uses a collation of SQL_Latin1_General_CP1_CI_AS, consider the following table:
SQL
CREATE TABLE T1(
C1 INT PRIMARY KEY,
C2 VARCHAR(10) collate Chinese_PRC_CI_AI)
CDC might fail to capture the binary data for column C2, because its collation is different (Chinese_PRC_CI_AI). Use nvarchar to avoid this problem:
SQL
CREATE TABLE T1(
C1 INT PRIMARY KEY,
C2 NVARCHAR(10) collate Chinese_PRC_CI_AI --Unicode data type, CDC works well with this data type
)
Currently, enabling both change data capture (CDC) and accelerated database recovery (ADR) isn't supported in SQL Server.
When you enable CDC, the aggressive log truncation feature of ADR is disabled. This is because the CDC scan accesses the database transaction log. Active transactions continue to hold the transaction log truncation until the transaction commits and CDC scan catches up, or the transaction aborts. If you enable CDC on a database where ADR is enabled, you might observe higher transaction log utilization. Ensure that sufficient transaction log space is available for the needs of all your workloads.
When enabling CDC, we recommend using the Resumable index option. Resumable index doesn't require to keep open a long-running transaction to create or rebuild an index, allowing log truncation during this operation and better log space management. For more information, see Guidelines for online index operations - Resumable Index considerations.
Online DDL statements are unsupported when change change data capture is enabled on a database.
When you enable CDC on a database, it creates a new schema and user named cdc. So manually creating a custom schema or user named cdc isn't recommended, as it's reserved for system use.
If you've manually defined a custom schema or user named cdc in your database that isn't related to CDC, the system stored procedure sys.sp_cdc_enable_db fails to enable CDC on the database with the following error message.
Output
The database <database_name> cannot be enabled for change data capture because a database user named 'cdc' or a schema named 'cdc' already exists in the current database. These objects are required exclusively by CDC. Drop or rename the user or schema and retry the operation.
To resolve this issue:
- Manually drop the empty
cdcschema andcdcuser. Then, CDC can be enabled successfully on the database.
When the data type of a column on a CDC-enabled table is changed from text to nvarchar, or image to varbinary, and an existing row is updated to an off-row value. After the update, the CDC scan will result in errors.
Changing the size of columns of a CDC-enabled table using DDL statements can cause issues with the subsequent CDC capture process, resulting in error 2628 or error 8115. Remember that data in CDC change tables are retained based on user-configured settings. So, before making any changes to column size, you must assess whether the alteration is compatible with the existing data in CDC change tables.
If the sys.dm_cdc_errors indicate that scans are failing due to the error 2628 or error 8115 for change tables, you should first consume the change data in the affected change tables. After that, you need to disable and then reenable CDC on the table to resolve the problem effectively.
When you enable CDC, a cdc user is created to manage the CDC creation process. The cdc user runs a number of stored procedures to enable CDC, and some of these stored procedures create objects which fire existing CREATE OBJECT triggers. Since the cdc user does not have permission to write to the master database, these CDC stored procedures fail with error 22830.
Disable any CREATE OBJECT triggers before enabling CDC on a database. Reenable these triggers after CDC is configured.
For CDC enabled SQL databases, when you use SqlPackage, SSDT, or other SQL tools to Import/Export or Extract/Publish, the cdc schema and user get excluded in the new database. Other CDC objects not included in Import/Export and Extract/Deploy operations include the tables marked as is_ms_shipped=1 in sys.objects.
Even if CDC isn't enabled and you've defined a custom schema or user named cdc in your database that will also be excluded in Import/Export and Extract/Deploy operations to import/setup a new database.
Using variables with partition switching on databases or tables with change data capture (CDC) isn't supported for the ALTER TABLE ... SWITCH TO ... PARTITION ... statement. See partition switching limitations to learn more.
This section steps to troubleshoot errors associated with CDC on SQL Server, and Azure SQL Managed Instance. CDC-related errors might obstruct the proper functioning of the capture process and lead to the expansion of the database transaction log.
To examine these errors, you can query the dynamic management view sys.dm_cdc_errors. If sys.dm_cdc_errors dynamic management view returns any errors, refer to the following section to understand the mitigation steps.
Napomena
For more information on a particular error code, see Database Engine events and errors.
These are the different troubleshooting categories included in this section:
| Category | Description |
|---|---|
| Metadata Modified | Includes information on how to mitigate issues related with CDC when the tracked tabled has been modified or dropped. |
| Database Space Management | Includes information on how to mitigate issues when the database space has been exhausted. |
| CDC Limitation | Includes information on how to mitigate issues caused by CDC limitations. |
Cause: The error might occur when CDC metadata has been dropped. For CDC to function properly, you shouldn't manually modify any CDC metadata such as
CDC schema, change tables, CDC system stored procedures, defaultcdc userpermissions (sys.database_principals) or rename thecdc user.Recommendation: To address this problem, you need to disable and re-enable CDC for your database. When enabling change data capture for a database, it creates the cdc schema, cdc user, metadata tables, and other system objects for the database.
Napomena
Objects found in the sys.objects system catalog view with is_ms_shipped=1 and schema_name='cdc' should not be altered or dropped.
Cause: The error might occur when
cdc userhas been dropped. For CDC to function properly, you shouldn't manually modify any CDC metadata such asCDC schema, change tables, CDC system stored procedures, defaultcdc userpermissions (sys.database_principals) or rename thecdc user.Recommendation: Ensure the
cdcuser exists in your database, and also has thedb_ownerrole assigned. To create thecdcuser, see the example Create cdc user and assign role.
Cause: This type of principal can't be impersonated, or you don't have permission. The error might occur when CDC metadata has been dropped or it's no longer part of the
db_ownerrole. For CDC to function properly, you shouldn't manually modify any CDC metadata such asCDC schema, change tables, CDC system stored procedures, defaultcdc userpermissions (sys.database_principals) or rename thecdc user.Recommendation: Ensure the
cdcuser exists in your database, and also has thedb_ownerrole assigned. To create thecdcuser, see the example Create cdc user and assign role.
Cause: This error happens when SQL Server can't find or access the replication system table '%s.' This could be because the table is missing or unreachable. For CDC to function properly, you shouldn't manually modify any CDC metadata such as
CDC schema, change tables, CDC system stored procedures, defaultcdc userpermissions (sys.database_principals) or rename thecdc user.Recommendation: Verify that the system table exists and is accessible by querying the table directly. Query the sys.objects system catalog, set predicate clause with is_ms_shipped=1 and schema_name='cdc' to list all CDC-related objects. If the query doesn't return any objects, you should disable and then re-enable CDC for your database. Enabling change data capture for a database creates the cdc schema, cdc user, metadata tables, and other system objects for the database.
Cause: The
cdc userhas been removed from thedb_ownerdatabase role, or from thesysadminserver role.Recommendation: Ensure the
cdc userhas thedb_ownerrole assigned. To create thecdcuser, see the example Create cdc user and assign role.
Error 22830 - Could not update the metadata that indicates database <database name> is enabled for Change Data Capture. The failure occurred when executing the command <CDC stored procedure name>.
Cause: This error occurs when a 'CREATE OBJECT' trigger exists in the database or on the server. When you enable CDC, a
cdc useris created to manage the CDC creation process. Thecdc userruns a number of stored procedures to enable CDC, and some of these stored procedures create objects which fire existingCREATE OBJECTtriggers. Since thecdc userdoes not have permission to write to themasterdatabase, these CDC stored procedures fail with error 22830.Recommendation: Before you enable CDC on a database, disable any
CREATE OBJECTtriggers. Reenable these triggers again after CDC is configured.
Cause: This error occurs when the primary filegroup of a database runs out of space, and SQL Server is unable to allocate more space for an object (such as a table or index) within that filegroup.
Recommendation: To resolve this issue, delete any unnecessary data within your database to free up space. Identify unused tables, indexes, or other objects in the filegroup that can be safely removed. Monitor space utilization closely, for more information, see Manage file space for databases in Azure SQL Database
In case dropping unnecessary data/objects is not an option, consider allocating more space for your database transaction log. For more information about transaction log management, see SQL Server transaction log architecture and management guide
Cause: Changing the size of columns of a CDC-enabled table using DDL statements can cause issues with the subsequent CDC capture process. The 'sys.dm_cdc_errors' Dynamic Management View (DMV) is a useful for checking any CDC for any reported issues, like errors number 2628 and 8115.
Recommendation: Before making any changes to column size, you must assess whether the alteration is compatible with the existing data in CDC change tables. To address this problem, you need to disable and re-enable CDC for your database. For more information about enabling CDC for a database or a table, see Enable CDC for a database and Enable CDC for a table.
Cause: This error occurs when enabling CDC on a table with system CLR datatype, making DML changes, and then making DDL changes on the same table while the CDC capture job is processing changes related to other tables.
Recommendation: The recommended steps are to quiesce DML to the table, run a capture job to process changes, run DDL for the table, run a capture job to process DDL changes, and then re-enable DML processing. For more information, see CDC capture job fails when processing changes for a table with system CLR datatype (geometry, geography, or hierarchyid).
If the cdc user was removed, you can manually add the user back.
Use the following T-SQL script, to create a user (cdc), and assign the proper role for the same (db_owner).
SQL
IF NOT EXISTS
(
SELECT *
FROM sys.database_principals
WHERE NAME = 'cdc'
)
BEGIN
CREATE USER [cdc]
WITHOUT LOGIN WITH DEFAULT_SCHEMA = [cdc];
END
EXEC sp_addrolemember 'db_owner', 'cdc';
To verify if cdc user belongs to either the sysadmin or db_owner role, run the following T-SQL query:
SQL
EXECUTE AS USER = 'cdc';
SELECT is_srvrolemember('sysadmin'), is_member('db_owner');
If the cdc user doesn't belong to either role, execute the following T-SQL query to add db_owner role to the cdc user.
SQL
EXEC sp_addrolemember 'db_owner' , 'cdc';