Home » Blog » Connections to a Database

Connections to a Database

2011-09-05 - General, SQL Server Internals

Sometimes you need to know who is currently connected to specific database.
While certainly not the only reason, the need for this information arises most often after receiving an error message like this one:

Msg 3702, Level 16, State 3, Line 1

Cannot drop database "AdventureWorks2008R2" because it is currently in use.

This error is informing you that the database can not be dropped right now, because someone is still using it. An error like this will show up for any attempt to execute an action that requires exclusive access to the database.

To proceed with the requested action you have several options. You could for example force the database offline:

[sql] ALTER DATABASE AdventureWorks2008R2 SET OFFLINE WITH ROLLBACK IMMEDIATE;
[/sql]

This solution has however several implications that make it unattractive to use. It also does not help you if you really just want to know who is connected to the database right now.

To just get to the information you could run this statement:

[sql] SELECT * FROM master..sysprocesses WHERE dbid = DB_ID('AdventureWorks2008R2');
[/sql]

This was the common recommendation to go with in SQL Server 2000. However, while it still works (even in SQL Server Denali CTP3) the sysprocesses system table has been deprecated in newer versions of SQL Server.

Instead you could go with the system procedure sp_who. It is not deprecated (as of Denali CTP 3), but it does not allow you to filter on a specific database, so if you have a lot of concurrent connections it gets quite cumbersome to use. There is also sp_who2, but it has the same shortcoming and it is also undocumented.

So lets look at the DMVs:

[sql] SELECT * FROM sys.dm_exec_sessions WHERE database_id = DB_ID('AdventureWorks2008R2')
[/sql]

This query gives you very similar information to the first one that queried from sysprocesses. It's biggest problem is that the database_id column was introduced in Denali, so you most likely will not be able to use it for a while.

There is a database_id column in the sys.dm_exec_requests DMV, but it returns rows only for processes that currently have an active request (statement) running. That means idle connections like an open SSMS query window are not included.

All these methods, besides of their individual problems, have one big shortcoming in common: The information they provide is incomplete.

To drop a database, the executing connection needs to acquire an exclusive lock on the database. This request, like any other lock request will be blocked if there is an incompatible lock held by another connection.

Every connection that does anything in or with a database first requests a lock on that database. Connections that retrieve or alter data in the database will acquire a shared lock on the database. Connections that, like a database drop, require exclusive access to the database require an exclusive lock on it. An exclusive lock — as the name suggests — is not compatible with any other lock, so the requesting process is blocked if any other lock on that database is currently held. SQL Server automatically sets the lock timeout for these statements to a fairly short value, so you will not be blocked forever like you would be in a normal blocking situation with the default lock timeout (infinite).

This means that a single connection, for example by doing a cross database query, can hold a lock on more than one database:

[sql] USE tempdb;
BEGIN TRAN
SELECT TOP(1)* FROM AdventureWorks2008R2.Person.Person;
[/sql]

This connection will hold a lock on AdventureWorks2008R2 while the dbid column in sysprocesses will point to tempdb.
So, the dbid column in sysprocesses as well as the soon to come database_id column in sys.dm_exec_sessions provide the information in which database the next statement will be executed in. The information we are really looking for, we need to get from somewhere else.

This information is provided by the sys.dm_tran_locks DMV:

[sql] SELECT request_session_id
FROM sys.dm_tran_locks
WHERE resource_type = 'DATABASE'
AND resource_database_id = DB_ID('AdventureWorks2008R2') ;
[/sql]

This query returns the session id for each session that is currently holding a lock on the AdventureWorks2008R2 database which is exactly the answer to our quest.

If you would like to see a little more information about the established connections, you can use the dbo.DatabaseConnections function:

[sql] IF OBJECT_ID('dbo.DatabaseConnections') IS NOT NULL
DROP FUNCTION dbo.DatabaseConnections ;
GO
CREATE FUNCTION dbo.DatabaseConnections (@DbId INT = NULL)
RETURNS TABLE
AS
RETURN
SELECT d.database_id,
d.name AS database_name,
c.session_id,
l.request_mode + '(' + l.request_status + ')' AS 'DbLock',
s.status,
c.auth_scheme,
s.login_name,
c.client_net_address,
s.host_name,
s.program_name,
s.host_process_id,
s.client_interface_name,
c.connect_time,
s.login_time,
s.last_request_start_time,
CASE WHEN r.session_id IS NULL THEN s.last_request_end_time
END AS last_request_end_time,
c.last_read,
c.last_write,
at.transaction_begin_time,
st.is_local AS is_local_transaction,
r.percent_complete,
CAST(t.text AS NVARCHAR(MAX)) text,
s.is_user_process,
s.transaction_isolation_level,
s.lock_timeout,
r.statement_start_offset,
r.statement_end_offset,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
c.net_transport,
c.net_packet_size,
s.total_elapsed_time,
s.total_scheduled_time,
s.cpu_time,
s.reads,
s.writes,
s.logical_reads,
c.num_reads,
c.num_writes,
s.memory_usage
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r
ON c.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle,
c.most_recent_sql_handle)) t
JOIN sys.dm_tran_locks l
ON l.request_session_id = c.session_id
AND l.resource_type = 'DATABASE'
AND l.resource_subtype = ''
JOIN sys.databases d
ON l.resource_database_id = d.database_id
LEFT JOIN sys.dm_tran_session_transactions st
JOIN sys.dm_tran_active_transactions at
ON at.transaction_id = st.transaction_id
ON st.session_id = c.session_id
WHERE d.database_id = COALESCE(@DbId, d.database_id) ;
GO
[/sql]

dbo.DatabaseConnections is a table valued function that requires the database id of the database of interest to be passed in. If you pass in a NULL it will return information about all databases.
For each connection, it provides information about who is connected from where using which software and what type of lock is held on the given database. It also gives information about when the connection was established and the last time for a handful of actions like sending a request or executing a data change. It shows if a transaction is open on the connection and if so, when it was started. It also returns the statement that is (or was) executing together with a little bit of statistical information.

One final note: You cannot use this function to see who is using master or tempdb. Because both databases are essential to SQL Servers functioning, SQL Server restricts what you can do to them. Particularly, you cannot drop or restore them. Therefore it is not necessary for SQL Server to track if there is a connection the still needs these databases around. To save the overhead of locking, connections do not need to acquire a database lock on either of the two databases. That in turn means, that the above function will not return any information about connections that are using only one of these two databases.

Categories: General, SQL Server Internals