SQL Server 2019 Producer Failure and Slowness of initial load data from Google Big Query

Question

When Google Big Query to process initial load data of table enable CDC function from database SQL Server 2019 Enterprise edition , Some found error message "Producer Failure" and can't continue initial load data, Please kindly to provide solution for solve this problem.

Answer 1

Hi @Piya Yaisamlee (Jeab) ,

I do not know much about Google Big Query, as you can see, you said SQL Server producer failure, you can share your error log which is located at: \Program Files\Microsoft SQL Server\MSSQLV.SQLEXPRESS\MSSQL\Log\ERRORLOG
It will collect information about errors occur in SQL Server

For more about Google Big Query, I cannot provide the appropriate help. It is recommended to seek help from the relevant forum

This doc about Google Big Query, Producer failure is mentioned in it, hope to give you a little tip:
https://cloud.google.com/data-fusion/docs/support/troubleshooting#replication-sql-server

https://cloud.google.com/data-fusion/docs/tutorials/replicating-data/sqlserver-to-bigquery

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Piya Yaisamlee (Jeab) ，

Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library

This error message means that there is some process trying to connect at SQL port, it is suggest to run network monitor to see what is hitting your server on the port

You can consider that
Network team doing port scanning
Note that anti-virus programs do check the port
MSSQL server is under a DDos

You should check the IP [CLIENT: 10.120.1.2] of the machine and for more serious case, you may want to restrict that IP

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @YufeiShao-msft
Thank you for your information,
For message "Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library"
Can we know the current network package for our current SQL? How to check ?, Please kindly to explain.

Answer 4

@YufeiShao-msft
Thanks you for your information, I have export an error log of sql server 2019 on that date (12/Jun/2022) have found a problem of error "Producer Failure" with information as below.
Note: Process STEP of Data Fusion CDC pipeline initial load data from SQL Server 2019 to Google Big Query to started from 12/Jun/2022 10:15AM to 12/Jun/2022 05:00 PM and when some time found problem of error "Producer Failure" then data engineer will to start run service of Data Fusion CDC pipeline again.

SQL Error log on 12/Jun/2022
=======START============
2022-06-12 00:00:01.30 spid22s This instance of SQL Server has been using a process ID of 5676 since 4/19/2022 7:21:42 PM (local) 4/19/2022 12:21:42 PM (UTC). This is an informational message only; no user action is required.
2022-06-12 05:13:32.25 spid66 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2022-06-12 05:13:32.26 spid66 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2022-06-12 05:13:32.27 spid66 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2022-06-12 05:15:29.33 spid54 AppDomain 3 (mssqlsystemresource.dbo[runtime].2) created.
2022-06-12 06:37:09.11 spid61 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2022-06-12 06:37:09.12 spid61 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2022-06-12 06:37:09.12 spid61 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2022-06-12 09:55:25.25 Logon Error: 17836, Severity: 20, State: 17.
2022-06-12 09:55:25.25 Logon Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 10.120.1.2]
2022-06-12 17:13:20.72 spid66 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2022-06-12 17:13:20.73 spid66 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2022-06-12 17:13:20.73 spid66 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2022-06-12 17:14:51.02 spid60 AppDomain 4 (mssqlsystemresource.dbo[runtime].3) created.
=======END============

Answer 5

@YufeiShao-msft
Thank you for your information, I have export an error log of SQL server 2019 on that date (12/Jun/2022) and have found a problem of error "Producer Failure" with information as below.
Note : Process STEP of Data Fusion CDC pipeline initial load data from SQL Server 2019 to Google Big Query to started process from 12/Jun/2022 10:15AM to 12/Jun/2022 05:00PM and when some time found a problem of error "Producer Failure" then data engineer will to start run service of Data Fusion CDC pipeline again.
SQL Error log on 12/Jun/2022

==========
START===============
2022-06-12 00:00:01.30 spid22s This instance of SQL Server has been using a process ID of 5676 since 4/19/2022 7:21:42 PM (local) 4/19/2022 12:21:42 PM (UTC). This is an informational message only; no user action is required.
2022-06-12 05:13:32.25 spid66 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2022-06-12 05:13:32.26 spid66 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2022-06-12 05:13:32.27 spid66 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2022-06-12 05:15:29.33 spid54 AppDomain 3 (mssqlsystemresource.dbo[runtime].2) created.
2022-06-12 06:37:09.11 spid61 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2022-06-12 06:37:09.12 spid61 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2022-06-12 06:37:09.12 spid61 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2022-06-12 09:55:25.25 Logon Error: 17836, Severity: 20, State: 17.
2022-06-12 09:55:25.25 Logon Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 10.120.1.2]
2022-06-12 17:13:20.72 spid66 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2022-06-12 17:13:20.73 spid66 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2022-06-12 17:13:20.73 spid66 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2022-06-12 17:14:51.02 spid60 AppDomain 4 (mssqlsystemresource.dbo[runtime].3) created.

==========
END===============