How can I find the exact process causing the Error: 17836 on my SQL server

SQL Server 2012 keeps giving me this error in the SQL Server logs: Length specified in network packet payload did not match number of bytes read; the connection has been closed. The client IP is the IP of the SQL Server. So this is coming from an internal request. How can I figure out exactly what command is causing this error and then eventually how to stop it.

I get 2 entires every day at the same time.

1. Error: 17836, Severity: 20, State: 17.
2. Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: IP OF MY SQL SERVER]

What type of trace can I run to identify exactly what command / job is causing this?

Thanks experts!
LVL 1
sqlagent007Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Open a query window and run the following command:
SELECT c.session_id, c.connect_time, c.client_net_address, c.client_tcp_port, s.host_name, s.program_name, s.host_process_id, s.login_name
FROM sys.dm_exec_connections c
	INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id

Open in new window

You'll be able to identify the Client IP Address and the Program that's running.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqlagent007Author Commented:
When I run this query, I see the SQL Agent sent the alert on the error message, but I am not seeing what command caused that error.

I get the errors every day about the same time in this order

1) Error: 17836, Severity: 20, State: 17.
2) Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library.
3) Error: 17836, Severity: 20, State: 17.

I want to see exactly what command caused this. It says the source is "logon", so that leads me to believe that something is attempting to logon to my server. However when running a trace, I did not see any external connections.
Vitor MontalvãoMSSQL Senior EngineerCommented:
After you identify the session id with the query I've posted before, use that id to run this query (example for sessionID=65):
SELECT t.*
FROM sys.dm_exec_requests r
	CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = 65

Open in new window

NOTE: Both queries are meant to be running immediately when the issue occurs. If you run it later then it won't catch the culprit as that session would be long expired.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sqlagent007Author Commented:
This is a good query, but only provides real time data...I guess I can create a job to run every 1 second during the the 1 minute this even may occur and capture those details....
Vitor MontalvãoMSSQL Senior EngineerCommented:
The only way you can go "back on time" to analyze old queries is storing them in a table for later usage and for that you'll need a continuous job to be running and capturing the necessary information.
sqlagent007Author Commented:
This was helpful, but I am still not able to pinpoint what is causing this error everyday at the same time.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you build any table to store the information so you can analyze it after?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Solution provided.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.