SQL Server Active processes in sleeping status?

1
votes

When I execute below TSql script, it gives number of active connections with respect to each host name.However is shows more than 200 active connections from one of the Developer workstation.In fact he do not have any active query windows open in SSMS.Please suggest why SQL Server Sys Processes table is showing the information incorrectly.

SELECT HostName,COUNT(*) As NoOfActiveConnections FROM SYS.SYsProcesses GROUP BY HostName Order By NoOfActiveConnections DESC

2
sqlsql-serversql-server-2005sql-server-2008
This is off topic for here I would have thought but any clues in the program_name column? – Martin Smith
@Martin Microsoft SQL Server Management Studio - Transact-SQL IntelliSense, this is what it is showing in Program_name column for all connections.I would have posted it in DBA forum however I dont see any SQL DBA tags. – Simhadri

2 Answers

0
votes

If the user is a developer then the answer will be in whatever they are working on. I can have no SSMS windows open but have a number of connections to the database because I am doing reports in SSRS or if I am working on a database related application. If the user is showing 200 connections it indicates their code is opening a lot of connections but never closing/disposing them.

0
votes

Grab sp_whoisactive and don't worry about parsing the information from sys.processes yourself. Adam Machanic did a month-long series exploring the power of the proc.