How to Track all the Applications and Clients connected to the Database in SQL Server?

If you need to trace all the connections from Applications and other Clients to your SQL Database you can do so with the following queries.

1.       This Query will list all the Applications that are connected to your database and also give the count of connections for each Application

 

 

--Application and Connections

Use DatabaseName

SELECT program_name AS 'Applications', COUNT(*) AS 'Connections'

FROM sys.sysprocesses

WHERE ecid=0

GROUP BY program_name

ORDER BY 'Connections' DESC;

 

2.       This Query will list all the Applications that are connected to your database and also give the Hostname on which those Applications run along with the count of connections for each Application.

 

--Hostname, Application and Connections

Use DatabaseName

SELECT hostname as 'HostName', program_name AS 'Applications',  COUNT(*) AS 'Connections'

FROM sys.sysprocesses

WHERE ecid=0

GROUP BY program_name, Hostname

ORDER BY HostName, Applications, 'Connections' DESC;

 

3.       This Query will list all the Applications that are connected to your database and also give the Hostname on which those Applications run along with the count of connections for each Application and also the Login Name the connections are using.

 

--Hostname, Application, LoginName and Connections

Use DatabaseName

SELECT hostname as 'HostName', program_name AS 'Applications', loginame,  COUNT(*) AS 'Connections'

FROM sys.sysprocesses

WHERE ecid=0

GROUP BY program_name, Hostname, loginame, nt_username

ORDER BY HostName, Applications, 'Connections' DESC;

Tags: