One Star

Talend SQL queries got suspended by SQL Server

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.
4 REPLIES
One Star

Re: Talend SQL queries got suspended by SQL Server

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
One Star

Re: Talend SQL queries got suspended by SQL Server

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?
One Star

Re: Talend SQL queries got suspended by SQL Server

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.
One Star

Re: Talend SQL queries got suspended by SQL Server

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