Informatica -SQL Error

  • 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.

  • 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!

  • 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.

  • Did a quick google and came up with this link:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9a68b2f5-a033-44a5-ab1b-ec76967ebfd4/protocol-error-in-tds-stream

    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!

  • This was removed by the editor as SPAM

  • 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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply