October 15, 2021 at 5:40 pm
We get a sql connection from host informatica server and the query runs in sql for almost 50 minutes and fails. We see the below information from informatica logs shared. We dont see any resource utilization or error logged in sql at this time frame. Any suggestion on what could be the reason.
Waiting for consumer to free buffer block. Please increase the dtm buffer pool size.
SQL Error [Microsoft SQL Server Native Client 11.0: Protocol error in TDS stream
Microsoft SQL Server Native Client 11.0: Protocol error in TDS stream SQL State: HY000 Native Error: 0
Microsoft SQL Server Native Client 11.0: Communication link failure SQL State: 08S01 Native Error: 10054
Microsoft SQL Server Native Client 11.0: TCP Provider: An existing connection was forcibly closed by the remote host.
SQL State: 08S01 Native Error: 10054
State: 1 Severity: 16 SQL Server Message: TCP Provider: An existing connection was forcibly closed by the remote host.
October 15, 2021 at 8:11 pm
I am pretty sure that the problem is with that buffer pool size as the error indicated. The error is saying that the connection was closed by the remote host. This means that the client (Informatica in your case) closed the connection to SQL before the query completed.
What I would recommend is investigate the query that is running slow and see if you can optimize it at the database side or the informatica side. On the database side, you could evaluate the query by using extended events or profiler and see the query that informatica is sending to SQL. Then you can run that on your system and grab an execution plan and see where you can tune things (indexes perhaps?).
Running for almost 50 minutes before erroring though is a bit crazy. I would reach out to Informatica and try to figure out what is happening. To me, this sounds like a configuration issue on Informatica.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 17, 2021 at 12:51 pm
Thanks for the details.
I too guessed it should be buffer pool size which cause this issue but they don't get this error when they run after some time.
I will try to fine tune the query as you suggested.
My app folks term this as a sql error since they see the word SQL in the informatica logs.
October 18, 2021 at 9:17 pm
Did a quick google and came up with this link:
Which suggests turning up the remote execution timeout period at the SQL server side OR hardware/network issues. Since the query runs for 50 minutes then fails, I expect it is the remote execution timeout period, but could be hardware or network issues too.
I would work on tuning that 50 minute query as to me that sounds excessive.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 22, 2021 at 6:48 am
This was removed by the editor as SPAM
October 25, 2021 at 4:21 pm
We did few query tuning and it seems the user are good so far. Thanks for your support on this. It helped me a lot.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply