7

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

atokpas
  • 8,680
  • 1
  • 18
  • 27
Ezeq
  • 117
  • 1
  • 3
  • 8

3 Answers3

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
Josh Darnell
  • 30,133
  • 5
  • 70
  • 124
Ian_H
  • 1,674
  • 10
  • 17
3

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.

user164767
  • 167
  • 1
  • 1
  • 7
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.

JuddGledhill
  • 21
  • 2