8

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

  • this one sounds like an odd request: on a moderately busy server a connection can be made a millisecond after you control query/sp/whatever is completed. what's the goal? why you are lookig for that information? what are you trying to accomplish? – Paolo Aug 4 '15 at 11:51
  • @Paolo Actually I am creating a DB upgrade scripts for that i need to check DB is in use or not. – MANISH Aug 4 '15 at 12:12
3

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.

| improve this answer | |
12

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>;
| improve this answer | |
0

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,

| improve this answer | |
  • OP said that he needed a query, not a stored procedure that would return the information. – RSax Aug 4 '15 at 11:33
  • @RSax ok i have updated the answer. Thanks – anishroniyar Aug 4 '15 at 11:40
  • It's not working. – MANISH Aug 4 '15 at 12:14
  • @Manish Any error? – anishroniyar Aug 4 '15 at 12:41
0

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
| improve this answer | |
  • This is not working showing error "Invalid column name 'db_id'. " – MANISH KUMAR CHOUDHARY Aug 5 '15 at 15:13
  • My code doesn't reference a column called db_id – RSax Aug 6 '15 at 8:53
-1

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
| improve this answer | |
-2

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
| improve this answer | |
  • 1
    I don't want to check online or offline I need to check no connection should be opened and currently no query is executing. – MANISH Aug 4 '15 at 10:20
  • 2
    This is wrong on two accounts: 1) sessions setting DB as context (which is the intent of the query) does not equal to DB use since a session with context in DB1 can reference (hence use) DB2 via 3 part names and 2) it uses deprecated views, at least use sys.dm_exec_sessions – Remus Rusanu Aug 5 '15 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.