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

Question

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

Answer 1

Although there are many answers posted I would like to post answer which is not technical but would surely tell you whether database is used or not. There is no PERFECT and ABSOLUTE way IMHO. What you can do is

1. You need to use all above methods speacially one suggested by Remus to see and get idea when was it last accessed.

2. Suppose in a big firm you want to figure out that database A is being used or not and who is using it. First shoot a mail to all required stakeholders, managers, and application owners that you are going to make Database A offline as you feel this is not used anymore. Make sure you include everybody and then wait for a day so that people would revert to your mail. If nobody reverts take full backup of the database and make database offline(dont drop it). Wait for another week or a month. You can do that as you already have made DB offline and it is not causing any load on server. Yes there might be monthly jobs running which needs that database once so I added to wait for a month. Even after a month if nobody reverts go ahead and do whatever you want to do with it.

Meanwhile if people complain about anything you know who is or which team is probably using it and then you can discuss further.

Even after making offline after a month someone comes and complains that certain job failed because you made DB offline you can very well say that you mailed about this quite before and there is no point in complaining now. In all cases you would come to know who is accessing and when.

PS: This is personally tried approach after referring to various solutions provided online and it has beautifully worked for me.

Answer 2

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 = <dbid>;

Answer 3

Use:

exec sp_who

in the sql-server.

This will give you all the information about the users, session and other information that are currently using your sql-server with status such as runnable, sleeping and so on.

You can also check about this in: https://msdn.microsoft.com/en-us/library/ms174313.aspx

OR if you only need a query then use:

select spid, status, loginame, hostname from master..sysprocesses

Thanks,

Answer 4

You are looking for two pieces of information:

1. The commands that are running in a database; and
2. Who is running those commands.

I believe this query will return the information you need, or at least provide you with the foundation that will serve your needs.

I adapted this code from examples I found in Books Online:

DECLARE @dbName varchar(100) = 'AdventureWorks2012'  --or the name of the db

SELECT
    z.DatabaseName,
    z.is_user_process,
    z.session_id as SPID,
    z.login_name,
    z.nt_domain,
    z.nt_user_name,
    z.original_login_name,
    y.cmd,
    y.status
FROM
    (SELECT
        db_name(s.database_id) as DatabaseName,
        s.is_user_process, 
        c.session_id, 
        s.login_name,
        s.nt_domain, 
        s.nt_user_name,
        s.original_login_name,
        s.host_name,
        s.program_name, 
        s.client_interface_name,
        c.connect_time, 
        s.login_time 
    FROM
        sys.dm_exec_connections AS c
    JOIN
        sys.dm_exec_sessions AS s
        ON c.session_id = s.session_id
    WHERE
        db_name(s.database_id) = @dbName --'AdventureWorks2012'
    ) AS z
JOIN
    (SELECT
        cmd,
        [status],
        spid,
        db_name(dbid) AS DatabaseName
    FROM
        sysprocesses
    WHERE 
        db_name(dbid) = @dbName --'Adventureworks2012'
    ) AS y
    ON
    z.DatabaseName = y.DatabaseName AND
    z.session_id = y.spid

Answer 5

Check below Query:

SELECT NAME AS dbname
 ,COUNT(STATUS) AS number_of_connections
 ,GETDATE() AS timestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME

Answer 6

To check how many Connections are open for the given database:

SELECT 
  DB_NAME(dbid) as DBName, 
  COUNT(dbid) as NumberOfConnections,
  loginame as LoginName
FROM
  sys.sysprocesses
WHERE 
  dbid > 0
GROUP BY 
  dbid, loginame