topic Re: Talend SQL queries got suspended by SQL Server in Talend Studio

Talend SQL queries got suspended by SQL Server

Anonymous — Sat, 16 Nov 2024 13:10:50 GMT

Hi
For several job involving a couple of tables my Talend SQL queries are suspended by SQL Server (2008 R2).
I get ASYNC_NETWORK_IO error, lasting from 1 min and then the job continues or I kill the job manually after like 5mins of pause.
Did this happened to anyone using Talend? I googled but found no mention of it.

Re: Talend SQL queries got suspended by SQL Server

Anonymous — Thu, 16 Dec 2010 09:52:30 GMT

My further research points to Talend not processing results quickly enough from SQL Server or this error is associated with a network performance issue.
More on ASYNC_NETWORK_IO: Troubleshooting ASYNC_NETWORK_IO

Re: Talend SQL queries got suspended by SQL Server

Anonymous — Thu, 16 Dec 2010 16:41:48 GMT

I am also getting total show stopper error in SQL Server activity monitor: LCK_M_IX
I am getting LCK_M_IX simultaneously with ASYNC_NETWORK_IO and it causes talend job to freeze.
It looks like Talend is trying to read and write the same record at the same time and it's getting suspended by SQL Server?

Re: Talend SQL queries got suspended by SQL Server

Anonymous — Thu, 16 Dec 2010 16:49:02 GMT

Only workaround is to specify input below let's say 100.000 records along with setting "Commit Every" and "Batch Size" in Outputs "Advanced settings" to 100.00 records also.
Only then Talend doesn't trigger above mentioned errors.

Re: Talend SQL queries got suspended by SQL Server

Anonymous — Thu, 16 Dec 2010 17:26:13 GMT

I found a solution.
Executing these queries on SQL Server 2008 R2 solves the problem:
ALTER DATABASE database
SET ALLOW_SNAPSHOT_ISOLATION OFF

ALTER DATABASE database
SET READ_COMMITTED_SNAPSHOT ON