How do I find out which database a connection is using?

Question

I'm using SQL Server 2008.

I'm trying to find out which connection within to my database server is using a database that I want to drop, so that I can kill that connection.

I can select session_id, login_name from sys.dm_exec_sessions etc to find sessions in the database, but how do I get from the session_id to the database to which the session is connected?

Answer 1

Try the following:

select db_name(DB_ID())

Answer 2

You need to join with the sys.sysprocesses table to find the database. You can then use db_name function to get the name.

Here is an example showing some of the (imho) most relevant fields for all connections within the last 24 hours

select 
    db_name(p.dbid),
    s.program_name, c.client_net_address, s.client_interface_name, s.host_name, s.login_name, s.nt_user_name, 
    c.connect_time, c.auth_scheme, c.local_net_address, c.local_tcp_port
FROM 
    sys.dm_exec_sessions s
    join sys.dm_exec_connections c ON s.session_id = c.session_id
    join sys.sysprocesses p on p.spid = c.session_id
where
    and connect_time > DATEADD(day,-1, GETDATE())
order by c.connect_time desc

Answer 3

Another way to find out how many connections exist on a certain database is calling the stored procedure:

sp_who2 active -- for active processes

sp_who2 -- all processes

Answer 4

Take a look here:

• http://msdn.microsoft.com/en-us/library/ms179881.aspx

Look at dbid column.