sql server get open connections

To get the active and open connections in SQL Server, you can use a few different methods depending on how much detail you need.

Here is the cleaned-up, fully corrected version of those scripts and explanations, free of formatting artifacts:

1. The Quick Count (By Database)

If you just want a quick headcount of how many connections are open on each database, query sys.sysprocesses:

SELECT 

    DB_NAME(dbid) as DatabaseName,     COUNT(dbid) as OpenConnections

FROM     sys.sysprocesses

WHERE     dbid > 0

GROUP BY     dbid;

2. The Detailed View (Recommended)

For a modern, detailed breakdown of who is connected, what application they are using, and where they are connecting from, use sys.dm_exec_sessions.

This script filters out internal system connections so you only see actual user traffic:

SELECT 

    session_id,    login_name,    host_name,    program_name,    status,    cpu_time,    total_elapsed_time

FROM      sys.dm_exec_sessions

WHERE      is_user_process = 1; -- 1 filters for user connections, 0 for system processes

3. What Are They Currently Running?

If you want to see the open connections and the exact SQL query they are executing right now, join the sessions DMV with sys.dm_exec_requests and sys.dm_exec_sql_text:

SELECT     s.session_id,    s.login_name,    s.host_name,    s.program_name,    r.status,    st.text AS ExecutingQuery

FROM     sys.dm_exec_sessions s

INNER JOIN     sys.dm_exec_requests r ON s.session_id = r.session_id

CROSS APPLY 

    sys.dm_exec_sql_text(r.sql_handle) st

WHERE     s.is_user_process = 1;

4. The Built-in Stored Procedure

If you don't want to type out a long query, SQL Server has a classic built-in stored procedure that gives you a massive dump of all current processes and connections

EXEC sp_who2;

 Look at the SPID column (anything above 50 is typically a user connection) and the DBName column to see where the action is happening.