6

I'm trying to find which application is opening various connections on the SQL Server, all named ".net sqlclient data provider".

From system monitor I got the machine name;
On the client with netstat -a -b -o | Find "SQLServer", I have found 4 connections all with the PID 4 (ntoskrnl) ... yes this is windows and it is a MS SQL server.

"Trace process in SQL server profiler", crashes the Management Studio.

So, before I start killing applications, one by one, do you have an idea how to trace back this connection?

Best Regards;
Ezeq

Improve this question
3
  • 2
    Have you tried sysinternals tools like ProcMon to get better info about running processes?
    – Aaron Bertrand
    Jan 17, 2017 at 4:40
  • No. To be honest I didn't install sysinternals, because until now I din't master any of it's tools. It looks like it's time to learn.
    – Ezeq
    Jan 17, 2017 at 21:59
  • wait, why were you trying to trace the process in SQL? If you have the hostname of the machine that is running the application, then you should go to that machine do the investigation there.
    – Jonathan Fite
    Jul 25, 2017 at 18:54

3 Answers 3

Reset to default
5

sp_who2 (MSDN) is always a good start, and you can query the sys.processes table or sys.dm_exec_connections (MSDN) DMV.

Alternatively something like Adam Machanic's sp_WhoIsActive can help find problem processes and queries.

If you can't find what you're looking for directly in SQL Server, you'll be able to find things like the hostname/IP address and loginame that will help you track down the culprit.

Sample code for querying DMV/sys.processes (you'll have to chop it about if you want to do more):

Select spid,hostname,hostprocess,program_name,nt_username, blocked, waittime, waittype, loginame,cmd,spid,waittype,waittime,lastwaittype,cpu,physical_io,memusage,login_time,last_batch,open_tran,status,net_address, t.text 
from sys.sysprocesses sp 
--JOIN sys.dm_exec_connections con ON con.session_id = sp.sid
CROSS APPLY( select text from sys.dm_exec_sql_text(sp.sql_handle))t 
--where hostname
order by sp.spid
Improve this answer
1
  • Hi @Ian_H; I hope to dig in your reply during this weekend (only time I have to think without being interrupted by users, vendors or chief). I have to add, that (late) last night, I did my educated guest an Killed a service, that had been added by an upgrade on the vendor software using that SQL server, and the connections where drooped.
    – Ezeq
    Jan 17, 2017 at 22:22
2

We are using a logging monitor and asking our app developers to update a property on the application connection string using the approach here:

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx

Basically the "App" or "Application Name" keyword on the ConnectionString.

Improve this answer
2
  • While it looks like the OP had an existing process that was not identifying itself further, this would be helpful as a proactive measure, to try to make as many such connections as possible better identified going forward.
    – RDFozz
    Jul 25, 2017 at 19:20
  • One year later and a few updates after, I had the same problem with the same app and still they didn't set the "Application Name". This time I'm going to send them the link.
    – Ezeq
    Aug 3, 2018 at 23:11
2

There is a column in SQL Server Profiler Events "ClientProcessID" get that value against those queries and match them with Task Manager-> process Detail-> process with the same ID. This way we can get the the real application name.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.