5

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

  • 2
    Have you tried sysinternals tools like ProcMon to get better info about running processes? – Aaron Bertrand Jan 17 '17 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 '17 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 '17 at 18:54
4

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
  • 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 '17 at 22:22
1

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.

  • 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 '17 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 '18 at 23:11
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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