Problems and Alerts

[DouglasSilva]

I compile a list of errors and warnings we've encountered across all tasks.

We're uncertain about the exact issues causing data loss. Essentially, we're considering replicating the recovery from the point where the task stopped at LSN. However, when data loss occurs, it raises doubts.

This list comprises issues from the past year:

SQL_ERROR: Query timeout expired
--------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SqlState: 08S01 NativeError. 10054 Message:[Microsoft][Server Native Client 11.0]TCP Provider.
An existing connection was forcibly closed by the remate host.
---------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SplState: 08001 NativeError: 53 Message: [Microsoft][SQL Server Native Client 11.0] Named Pipes Provider: Could not open a connection to SQL Server[53].
---------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SqlState: 42000 NativeError: 983 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
Unable to access availability database *XYZ* because the database replica is not in the PRIMARV or SECONDARY role. Connections to an
availability database is permitted only when the database replica is in the PRIMARY ar SECONDARY role. Try the operation again later.
---------------------------------------------------------------------------------------------------------------------------------------
SqlStat: HYOOO NativeError:0 [Server Native Client 11.O]Unspecified error occurred an SQL Server. Connecticn may have been terminated by the server.
---------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SplState: 08S01 NativeError. 121 Message: [Microsoft][SQL Server Native Client 11.0]TCP Provider: The semaphore timeout period has expired.
---------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SqlState: HYOOO NativeError: O Message: [Microsoft][SQL Server Native Client 11.0]Unspecified error occurred on SQL Server.
Connection may have been terminated by the server.
---------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SqlState: 08001 NativeError: 258 Message: [Microsoft][SQL Server Native Client 11.0]TCP Provider. Timeout error.
---------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SqlState: 08S01 NativeError. 64 Message: [Microsoftl[SQL Server Native Client 11.0]TCP Provider: The specified network name is no longer available.
---------------------------------------------------------------------------------------------------------------------------------------
Retcade: SQL_ERROR sqlstate: 25000 NativeError: 3902 Message: s[Microsoftl[SQL Server Native Client 11.0] The COMMIT TRANSACTION request has na correspanding BEGIN TRANSACTION.
---------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SqlState: 28000 NativeError: 18456 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed far user 'xpto'.
---------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SplState: 42000 NativeError: 9002 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
The transaction log far database 'xpto' is full due to 'REPLICATION'.
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ERROR sqlstate: 08S01 NativeError. 0 Message: [Microsoftl[ODBC Driver 18 for SQL Server]Communication link failure
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ERROR sqlstate: 22007 NativeError: 295 Message: [Microsaft][SQL Server Native Client 11.O][SQL Server]
Conversion feiled when converting character string to smalldatetime data type Line: 1 Column
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ERROR sqlstate: 08001 NativeError: -2146893051 Message: [Microsoftl[SQL Server Native Client 11.0]SQL Server Network Interfaces:
The requested security package does not exist
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ERROR sqlstate: 40001 NativeError: 1205 Message: Server Native Client 1 ServerlTransaction (Process ID 61) wes deadlocked on lock resources
with another process and has been chosen as the deadlock victim. Rerun the transaction.
---------------------------------------------------------------------------------------------------------------------------------------
Retcade: SQL_ERROR sqlstate: 42000 NativeError: 3201 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
Cannot open backup device operating system error 2(The system cannot find the file specified.)
---------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SplState: 42000 NativeError: 9002 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
The transaction log far database 'xpto' is full due to 'ACTIVE_TRANSACTION'.
---------------------------------------------------------------------------------------------------------------------------------------
RetCade: SQL_ERROR SqlState: 42000 NativeError: 922 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Database 'msdb• is being recovered.
Waiting until recovery is finished.

[PGN]

Have you invited your friendly neighborhood DBA to look at these errors?  Some of them indicate that the server may be in the process of starting or stopping (e.g., Unable to access availability database *XYZ* because the database replica is not in the PRIMARV or SECONDARY role. Connections to an
availability database is permitted only when the database replica is in the PRIMARY ar SECONDARY role. Try the operation again later. or RetCade: SQL_ERROR SqlState: 42000 NativeError: 922 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Database 'msdb• is being recovered.
Waiting until recovery is finished."  

Some are related to timeouts/deadlocks either on the source or target (SQL_ERROR sqlstate: 40001 NativeError: 1205 Message: Server Native Client 1 ServerlTransaction (Process ID 61) wes deadlocked on lock resources
with another process and has been chosen as the deadlock victim. Rerun the transaction. or RetCade: SQL_ERROR SqlState: 08001 NativeError: 258 Message: [Microsoft][SQL Server Native Client 11.0]TCP Provider. Timeout error.)

It's difficult to say whether these are causing data loss.  If you do data comparisons and then go back and research whether there were any issues reported during that time, you might be able to identify how to resolve the issues. 

[Dana_Baldwin]

Hi @DouglasSilva 

I agree with @PGN 's general assessment. It is difficult to determine if any of these messages relates to loss of data. I suggest opening a support case to further investigate.

Warnings are usually not critical and are logged for your information. Log entries for warnings include "]W" whereas errors are denoted with "]E".

Are these new tasks? You might consider getting help from our Professional Services team. Check with your Account Manager on this.

SQL_ERROR: Query timeout expired: This generally is a performance issue on the source or target depending on which component in the log showed the error. It could also be a network performance issue. There are timeout parameters in the source & target endpoints, internal parameters on the advanced tab that can be increased to help alleviate this, but it won't solve the root cause. Type an exclamation point into the "search parameter" field and scroll down and select the ones with "timeout" in the name, there should be 2 or 3 but it depends on the endpoint and if it is a source or target. Try with 3x or 4x the default value, but you may need to increase them further. Tasks using these endpoints will need to be stopped and resumed before the new settings take effect (endpoint settings are read/processed upon task startup). This transaction will be retried and should not cause data loss.

RetCade: SQL_ERROR SqlState: 08S01 NativeError. 10054 Message:[Microsoft][Server Native Client 11.0]TCP Provider.
An existing connection was forcibly closed by the remate host.

Database may have gone down or been put in administrative mode so only a single user had access during maintenance/recovery. A DBA may also have forced the Replicate user connection to close.

RetCade: SQL_ERROR SplState: 08001 NativeError: 53 Message: [Microsoft][SQL Server Native Client 11.0] Named Pipes Provider: Could not open a connection to SQL Server[53].

Generally a network or DNS issue.

SqlStat: HYOOO NativeError:0 [Server Native Client 11.O]Unspecified error occurred an SQL Server. Connecticn may have been terminated by the server.

Ask the DBA to check the SQL Server logs for errors.

RetCade: SQL_ERROR SplState: 08S01 NativeError. 121 Message: [Microsoft][SQL Server Native Client 11.0]TCP Provider: The semaphore timeout period has expired.

Generally a network related issue. See: The semaphore timeout period has expired. - Microsoft Q&A

RetCade: SQL_ERROR SqlState: 08S01 NativeError. 64 Message: [Microsoftl[SQL Server Native Client 11.0]TCP Provider: The specified network name is no longer available.

Appears to be a network or DNS issue.

RetCade: SQL_ERROR SqlState: 28000 NativeError: 18456 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed far user 'xpto'.

Authentication issue on source or target, incorrect password in the endpiont?

RetCade: SQL_ERROR SplState: 42000 NativeError: 9002 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
The transaction log far database 'xpto' is full due to 'REPLICATION'.

DBA can help with this. May need the transaction log backup to run more often. More disk space may be needed for the online transaction log, or the max size limit increased.

SQL_ERROR sqlstate: 08S01 NativeError. 0 Message: [Microsoftl[ODBC Driver 18 for SQL Server]Communication link failure

Network issue.

SQL_ERROR sqlstate: 22007 NativeError: 295 Message: [Microsaft][SQL Server Native Client 11.O][SQL Server]
Conversion feiled when converting character string to smalldatetime data type Line: 1 Column

Could prevent this specific data from being processed. Please open a support case.

SQL_ERROR sqlstate: 08001 NativeError: -2146893051 Message: [Microsoftl[SQL Server Native Client 11.0]SQL Server Network Interfaces:
The requested security package does not exist

I'm not familiar with this message but seems related to configuration of tasks or the environment.

SQL_ERROR sqlstate: 40001 NativeError: 1205 Message: Server Native Client 1 ServerlTransaction (Process ID 61) wes deadlocked on lock resources
with another process and has been chosen as the deadlock victim. Rerun the transaction.

DBA can help with this. If you're seeing this on the target, do you have other applications modifying data there while the task runs?

Retcade: SQL_ERROR sqlstate: 42000 NativeError: 3201 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
Cannot open backup device operating system error 2(The system cannot find the file specified.)

Looks like task tried to read from a backup TLOG that was no longer available, or we attempted to read it from a shared drive that was not available at the time.

Hope this helps!

[deepaksahirwar · Accepted solution]

Hello @DouglasSilva ,

Welcome! We’re delighted to have you in the Qlik Community portal. Thank you for bringing your question to us.

I understand that you’re facing several SQL errors. Here are some possible solutions or explanations for the errors you’ve encountered:

1.SQL_ERROR: Query timeout expired:
This error occurs when Qlik Replicate is working on a query to the source or target and the query takes longer than anticipated, leading to the timeout1. You can edit the source or target endpoint and within the Advanced properties and add a new Internal Parameter named cdcTimeout.

2.RetCade: SQL_ERROR SqlState: 08S01 NativeError. 10054 Message:[Microsoft][Server Native Client 11.0]TCP Provider.
An existing connection was forcibly closed by the remote host: This error is due to network connectivity issues. These can be difficult to troubleshoot when the error is intermittent but you can verify network connectivity at a given time with PowerShell command Test-NetConnection -ComputerName "YourSqlServer" -Port 14332.

3. RetCade: SQL_ERROR SplState: 08001 NativeError: 53 Message: [Microsoft][SQL Server Native Client 11.0] Named Pipes Provider:
Could not open a connection to SQL Server[53]: This error could be due to an access control list that only allows certain authorized programs to make the connection. Check with your network administrator if they have such a list in place.

4. RetCade: SQL_ERROR SqlState: 42000 NativeError: 983 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
Unable to access availability database XYZ because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later: This error is received when one of the secondary cluster nodes is set to read-intent_only. Setting it to “Yes” should solve the issue.

5. SqlStat: HYOOO NativeError: [Server Native Client 11.O]Unspecified error occurred an SQL Server. Connection may have been terminated by the server:
This error could be due to a problem with the SQL Server Configuration Manager5. You can try to open up ‘SQL Server Configuration Manager’, select ‘SQL Native Client Configuration’, right-click on TCP/IP and select enable, and then restart SQL Server Service.

6.RetCade: SQL_ERROR SplState: 08S01 NativeError. 121 Message: [Microsoft][SQL Server Native Client 11.0]TCP Provider:
The semaphore timeout period has expired: This error could be resolved by configuring the Microsoft SQL database to increase the Microsoft SQL timeouts.

7.RetCade: SQL_ERROR SqlState: HYOOO NativeError: O Message: [Microsoft][SQL Server Native Client 11.0]Unspecified error occurred on SQL Server.
Connection may have been terminated by the server: This error could be due to a problem with the SQL Server Configuration Manager. You can try to open up ‘SQL Server Configuration Manager’, select ‘SQL Native Client Configuration’, right-click on TCP/IP and select enable, and then restart SQL Server Service.

8.RetCade: SQL_ERROR SqlState: 08001 NativeError: 258 Message: [Microsoft][SQL Server Native Client 11.0]TCP Provider. Timeout error:
This error could be resolved by configuring the Microsoft SQL database to increase the Microsoft SQL timeouts.

9.RetCade: SQL_ERROR SqlState: 08S01 NativeError. 64 Message: [Microsoftl[SQL Server Native Client 11.0]TCP Provider:
The specified network name is no longer available: This error is due to network connectivity issues. These can be difficult to troubleshoot when the error is intermittent but you can verify network connectivity at a given time with PowerShell command Test-NetConnection -ComputerName "YourSqlServer" -Port 14332.

10.Retcade: SQL_ERROR sqlstate: 25000 NativeError: 3902 Message: s[Microsoftl[SQL Server Native Client 11.0]
The COMMIT TRANSACTION request has na corresponding BEGIN TRANSACTION: This error could be due to a missing BEGIN TRANSACTION statement before the COMMIT TRANSACTION request.

11.RetCade: SQL_ERROR SqlState: 28000 NativeError: 18456 Message: [Microsoft][ODBC Driver for SQL Server][SQL Server]
Login failed for user ‘xpto’: This error indicates that the login details for the user ‘xpto’ are incorrect or that the user does not have the necessary permissions to access the database.

RetCade: SQL_ERROR SplState: 42000 NativeError: 9002 Message: [Microsoft][ODBC Driver for SQL Server][SQL Server]
The transaction log for database ‘xpto’ is full due to ‘REPLICATION’: This error indicates that the transaction log for the database ‘xpto’ is full due to replication. You can try to stop the Log Reader SQL server agent job and run sp_repldone to mark everything as replicated.

Please note that these are general solutions as per my experiences. Kindly go through each and try to find the root cause.

I hope this information helps! If you have any other questions, feel free to ask.

If our response has been helpful, please consider clicking “Accept as Solution”. 
This will assist other users in easily finding the answer. ‌‌

Best Regards,
Deepak