Thursday, September 13, 2018

SQL CDC fail after servicepack upgrade



Issue
·         SQL CDC job fail
·         Database log space usage can’t clear

Troubleshooting
·         SQL CDC job fail with error message
o   Msg 8144, Level 16, State 2, Procedure or function sp_batchinsert_1582628681 has too many arguments specified. For more information, query the sys.dm_cdc_errors dynamic management view
o   Msg 8144, Level 16, State 2, Procedure or function sp_batchinsert_690101499 has too many arguments specified. For more information, query the sys.dm_cdc_errors dynamic management view
·         Error message in sqlerror log
o   Could not open database [xxxxxx]. Replication settings and system objects could not be upgraded. If the database is used for replication, run sp_vupgrade_replication in the [master] database when the database is available.
·         Error in sys.dm_cdc_errors
session_id
phase_number
entry_time
error_number
error_severity
error_state
error_message
1
7
9/11/18 2:20
8144
16
2
Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
1
7
9/11/18 2:20
22863
16
1
Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
2
7
9/11/18 2:22
8144
16
2
Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
2
7
9/11/18 2:22
22863
16
1
Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
3
7
9/11/18 2:24
8144
16
2
Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
3
7
9/11/18 2:24
22863
16
1
Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
4
7
9/11/18 2:26
8144
16
2
Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
4
7
9/11/18 2:26
22863
16
1
Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
5
7
9/11/18 2:28
8144
16
2
Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
5
7
9/11/18 2:28
22863
16
1
Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
6
7
9/11/18 2:30
8144
16
2
Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
6
7
9/11/18 2:30
22863
16
1
Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.

·         log_reuse_wait_desc for the database = REPLICATION
·         Log space usage for xxxxxx = 80% of 320 gb
·         Run Log backup for xxxxxx and log usage can’t be clear

Finding
·         If a CDC enabled database is part of an Always On availability group and users follow the general recommendations of upgrading the secondary replica first, sp_vupgrade_replication will not run in such databases during upgrade because secondary replica databases are not in read/write mode. This is a known behavior and is by design.

Workaround
·         Run sp_vupgrade_replication in master db and issue solve
Reference

No comments: