DBA Scripts: Get Active Connections by Database

---

The Check Before You Take Anything Offline

Taking a database offline, whether for a decommission, a restore, or a maintenance operation that needs exclusive access, starts with the same question: is anyone actually using it right now? Guessing wrong means an unexpected outage for whoever was still connected.

This script answers that in one pass across every database at once: session count, active requests, open transactions, and blocked sessions, so you can see at a glance which databases are genuinely quiet and which ones aren’t.

---

Why Active Connections by Database Matters

• A database can look unused from the application side (no recent deploys, no known active project) while still carrying real, active sessions from a forgotten integration or a scheduled job
• open_transaction_count catches something a simple session count can’t: a database with an open transaction sitting idle is not safe to touch, even if nothing is actively running against it
• oldest_conn_min surfaces long-lived connections, useful for spotting connection pool leaks or an application that isn’t cycling its connections as expected
• Checking every database in one pass, rather than one at a time, is what makes this practical to run routinely rather than only during an incident

---

When to Run This Script

• Immediately before taking any database offline, restoring over it, or starting a decommission
• Investigating unexpected connection counts or a suspected connection leak
• Routine health checks, to build a baseline sense of which databases are quiet and which are genuinely busy
• Before a maintenance window that needs exclusive database access, to confirm the window will actually be uncontested

---

The Script

/*
Script Name : Get-ActiveConnectionsByDatabase
Category    : monitoring
Purpose     : Session count, active requests, open transactions, and blocked sessions grouped by database — essential check before taking any database offline or starting a decommission.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-active-connections-by-database/)
Requires    : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-active-connections-by-database/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    DB_NAME(s.database_id) AS database_name,
    COUNT(*) AS total_sessions,
    SUM(CASE WHEN s.status = 'running' THEN 1 ELSE 0 END) AS active_requests,
    SUM(CASE WHEN s.open_transaction_count > 0 THEN 1 ELSE 0 END) AS open_transactions,
    SUM(CASE WHEN r.blocking_session_id > 0 THEN 1 ELSE 0 END) AS blocked_sessions,
    COUNT(DISTINCT s.login_name) AS distinct_logins,
    MAX(DATEDIFF(MINUTE, s.login_time, GETDATE())) AS oldest_conn_min,
    MIN(s.login_time) AS oldest_login_time
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
WHERE s.is_user_process = 1
  AND s.database_id > 0
GROUP BY s.database_id
ORDER BY total_sessions DESC, active_requests DESC;

is_user_process = 1 filters out SQL Server’s own background and system sessions, so the counts reflect real application and user activity only.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Session counts and activity, grouped by database:
.\run.ps1 Get-ActiveConnectionsByDatabase

# To run against a remote sql server:
.\run.ps1 Get-ActiveConnectionsByDatabase -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/monitoring/Get-ActiveConnectionsByDatabase.sql

---

Example Output

Real output from this lab instance, not staged:

database_name	total_sessions	active_requests	open_transactions	blocked_sessions	oldest_conn_min
msdb	4	0	0	0	1690
master	3	1	0	0	1699

Every user database on this lab box shows zero sessions right now, only msdb and master carry connections (background tooling and this very session), a genuinely quiet instance. oldest_conn_min in the high 1600s (over a day) reflects long-lived tooling connections, not a leak, worth confirming that context before treating a large number here as automatically concerning.

---

Understanding the Results

• A database with zero rows — genuinely no active sessions right now, the safest possible state to take it offline or restore over
• open_transactions > 0 — treat this as a hard stop regardless of session count; an open transaction means something is mid-work even if the session shows as idle
• blocked_sessions > 0 — worth investigating before proceeding with anything that adds load, blocking already in progress will only get worse under additional pressure
• oldest_conn_min unexpectedly high — check whether this reflects legitimate long-lived tooling (like this lab’s own monitoring connections) or an application that isn’t recycling connections as designed

---

Best Practices

• Run this immediately before any operation requiring exclusive database access, don’t rely on assumptions about which databases are “known to be unused”
• Treat any nonzero open_transactions as a blocker, not just a number to note, confirm what’s holding it open before proceeding
• Build a routine baseline of normal connection patterns per database, so an unusual spike or an unexpectedly quiet database both stand out
• Cross-reference a surprising result with Server Inventory scripts if a database shows activity nobody expected

---

Related Scripts

You may also find these scripts useful:

• Blocking and Locking (hub)
• Database Snapshot Inventory
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

“Is anyone using this database” is a question worth answering with data, not an assumption, before any operation that needs exclusive access. This script answers it across every database in one pass, and the open_transaction_count column catches the specific case a simple connection count would miss: a session sitting idle while still holding a transaction open.

Run it immediately before any decommission, restore, or exclusive-access maintenance window, and treat any nonzero open transaction as a hard stop until you know what’s holding it.