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.

kill many process in sql server

 To kill multiple processes in SQL Server, you cannot pass a list of IDs directly into a single KILL command. Instead, you have to generate and execute individual KILL <spid> statements.

Here are the three most efficient ways to do this, depending on your needs.

Method 1: The Quick Filter Script (Recommended)

If you want to kill processes tied to a specific database or a specific user, you can run a query that automatically generates the syntax for you.

Run this script to generate the list of kill commands:

SQL

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += 'KILL ' + CAST(session_id AS VARCHAR(10)) + ';' + CHAR(13)

FROM sys.dm_exec_sessions

WHERE database_id = DB_ID('YourDatabaseName') -- Filter by database

  AND is_user_process = 1                     -- Only target user processes, not system tasks

  AND session_id <> @@SPID;                   -- Do not kill your current connection

-- STEP 1: Preview the commands to make sure they look correct

PRINT @sql;

-- STEP 2: Uncomment the line below when you are ready to execute them all at once

-- EXEC sp_executesql @sql;

Method 2: The Cursor Approach (Fully Automated)

If you want a single script that finds and drops connections instantly without a copy-paste preview step, you can loop through them using a CURSOR. This is highly effective if you are preparing a database for a restore or a drop.

SQL

DECLARE @spid INT

DECLARE @kill_cmd NVARCHAR(50)

DECLARE spid_cursor CURSOR FOR

SELECT session_id

FROM sys.dm_exec_sessions

WHERE database_id = DB_ID('YourDatabaseName')

  AND is_user_process = 1

  AND session_id <> @@SPID;

OPEN spid_cursor

FETCH NEXT FROM spid_cursor INTO @spid

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @kill_cmd = 'KILL ' + CAST(@spid AS VARCHAR(10))

    EXEC sp_executesql @kill_cmd

    FETCH NEXT FROM spid_cursor INTO @spid

END

CLOSE spid_cursor

DEALLOCATE spid_cursor

Method 3: Kick Everyone Off via ALTER DATABASE

If your goal is to kill every single connection to a specific database right now (for instance, to perform maintenance, rename it, or drop it), the fastest and safest approach doesn't require looking up SPIDs at all. You can forcefully transition the database into SINGLE_USER mode, which automatically terminates all other active connections.

SQL

USE master;

ALTER DATABASE YourDatabaseName

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Perform your work here...

-- Don't forget to set it back to multi-user mode when finished!

ALTER DATABASE YourDatabaseName

SET MULTI_USER;

Note on ROLLBACK IMMEDIATE: This option specifies that any open transactions in the database will be instantly rolled back, and their underlying connections severed immediately.

Best Practices & Precautions

Check Status: If you kill a long-running write transaction (like a massive data import or index rebuild), SQL Server must roll back the changes to ensure data integrity. If you check sys.dm_exec_requests, you might see the status as KILLED/ROLLBACK. Do not restart the SQL service to speed this up, as the rollback will simply resume upon startup and often take longer.

Avoid System SPIDs: Always include is_user_process = 1 or filter out session_id <= 50 to avoid accidentally targeting background SQL Server engine processes.

Cheers

Samitha