SQL Server: Quick way to find Active & idle connections on a SQL instance

Using SQL Server 2005 you can take help of DMV - sys.dm_exec_connections that will give server-level information about the connections. In the olden days usage of sysprocesses system table used to be heavy and same approach can be achieved by using system DMVs within 2005 version onwards.

All these DMVs -  sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests dynamic management views map to the sys.sysprocesses system table. So if you have any adhoc code that used to get connections with active/idle then change it to use DMVs.

For the previous versions you can use:

select last_batch, datediff(mi,last_batch, getdate()) , spid, * from sys.sysprocesses

or

select spid from sys.sysprocesses  where ( datediff(mi,last_batch, getdate()) > 5)