- cdc.dbnamexxx_capture
- cdc.dbnamexxx_cleanup
1: Msg 22859, Level 16, State 2, Log Scan process failed in processing log records.
2: Refer to previous errors in the current session to identify the cause and correct3: any associated problems. For more information, query the sys.dm_cdc_errors dynamic management view.
and
1: The Log-Scan Process failed to construct a replicated command from
2: log sequence number (LSN) {000015a9:0000395a:001b}.3: Back up the publication database and contact Customer Support Services.
4: [SQLSTATE 42000] (Error 18805) Log Scan process failed in processing log records.5: Refer to previous errors in the current session to identify the cause and correct6: any associated problems. [SQLSTATE 42000] (Error 22859)
7: The statement has been terminated. [SQLSTATE 42000] (Error 3621)
8: The statement has been terminated. [SQLSTATE 01000] (Error 3621)
9: The call to sp_MScdc_capture_job by the Capture Job for database 'xxxxxx' failed.10: Look at previous errors for the cause of the failure. [SQLSTATE 42000] (Error 22864)
There are two system DMV you can query to see the errors rather than see the job logs.
- sys.dm_cdc_log_scan_sessions
- sys.dm_cdc_errors
1) Before I execute these 2 queries, I thought the 2nd error log was the solution key. So I’ve done something but they were wrong, DO NOT use the solution
- Change DB to FULL recovery mode, I though it needs to read log. and FULL backup and LOG backup. But it still failed, why?
2) Then I query the DMV sys.dm_cdc_errors, it showed more details:
1: The filegroup "SECONDARY" has no files assigned to it. Tables, indexes,2: text columns, ntext columns, and image columns cannot be populated
3: on this filegroup until a file is added.
and
1: Cannot insert duplicate key row in object 'cdc.dbo_xxxx_CT' with unique index2: 'dbo_xxxx_CT_Index’. The duplicate key value is (2).
Is the “The filegroup "SECONDARY" “ the solution? Because I found the CDC tables located at another filegroup but assigned to “SECONDARY” filegroup, so I follow the instruction below to adjust it
1: USE [CDC Database]2: DBCC SHRINKFILE ('CDC File name',EMPTYFILE) – it located at the wrong filegroup3: ALTER DATABASE [CDC Database] REMOVE FILE [CDC File name]
3) So, I backup the Log. then1: Msg 1833, Level 16, State 2, Line 32: File 'CDC File name' cannot be reused until after the next BACKUP LOG operation.3: If the database is participating in an availability group,4: a dropped file can be reused only after the truncation LSN of the primary5: availability replica has passed the drop LSN of the file and a subsequent6: BACKUP LOG operation has completed.
1: ALTER DATABASE [CDC Database] ADD FILE to FILEGROUP [SECONDARY]
The CDC job is still failed, so I have to do is compromise the CDC data lost and recreate CDC tables
- EXEC sys.sp_cdc_help_change_data_capture
- Disable CDC tables (CDC table will be dropped, but the original table wont be, so don’t worry)
- Re-enable CDC table and assign to the correct role and filegroup
Below are some of queries to help you identify the issues
--01, Check the last database restore time
SELECT [rs].[destination_database_name], [rs].[restore_date],[bs].[backup_start_date], [bs].[backup_finish_date],[bs].[database_name] as [source_database_name],[bmf].[physical_device_name] as [backup_file_used_for_restore]FROM msdb..restorehistory rsINNER JOIN msdb..backupset bsON [rs].[backup_set_id] = [bs].[backup_set_id]INNER JOIN msdb..backupmediafamily bmfON [bs].[media_set_id] = [bmf].[media_set_id]ORDER BY [rs].[restore_date] DESC
--02, check are there any Restore, backup running
SELECT session_id AS SPID, command, a.TEXT AS Query,start_time, percent_complete,dateadd(second, estimated_completion_time / 1000, getdate()) AS estimated_completion_timeFROM sys.dm_exec_requests rCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) aWHERE r.command IN ('BACKUP DATABASE',
'RESTORE DATABASE'
,'BACKUP LOG' )
or r.command like '%RESTORE%'
--03, Check whether DB’s CDC is enabled
USE master;SELECT @@SERVERNAME, [name], [database_id], [is_cdc_enabled]FROM sys.databases;
--04, Check whether tables are CDC enabled for the DB
USE [Yoour DB name]SELECT DB_NAME(), [name], [is_tracked_by_cdc]FROM sys.tables;
--05, If you want to disable CDC table,
--use the SP to find out the source instance
Use [CDC enabled DB]EXEC sys.sp_cdc_help_change_data_capture
--06, find out some error info
SELECT * FROM sys.dm_cdc_log_scan_sessionsSELECT * FROM sys.dm_cdc_errors
1 comment
These options contain digitization of information, parts-building assist, personalized know-how growth based mostly on utility wants, custom supplies, and supporting these with utility and enterprise experience. Belgian firm Materialise has a 30-year historical past providing 3D printing options and related software program. It provides platforms to facilitate the event of 3D printing purposes in industries similar Space Heaters to healthcare, automotive, aerospace, and artwork and design. Some of the company's first 3D printing actions included anatomical models in each dental and listening to help merchandise.
Post a Comment