snippetsqlModerate

How to find SQL Server database is in use or not?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

howsqldatabasefindserverusenot

Problem

What query do I need to write to know if the database is in use or not? I tried following query

SELECT
    name 
FROM 
    sys.databases 
WHERE 
    state != 0;

but it is not working.

I want to check there should not have a open connection with that database and should not executing a query against that DB

In order to use a database any session must first obtain a lock on it, usually a shared lock. So simply look for such locks:

select * 
from sys.dm_tran_locks 
where resource_type = 'DATABASE' 
and resource_database_id = ;

select * 
from sys.dm_tran_locks 
where resource_type = 'DATABASE' 
and resource_database_id = <dbid>;

StackExchange Database Administrators Q#109019, answer score: 14

No revisions yet.