Who’s using that database?

Leave a comment

March 9, 2015 by Kenneth Fisher

A common problem when trying to alter a database (take it offline, add a filegroup, whatever) is that someone else is in the database and you need to find them and kick them out before you can proceed. Historically I would use sp_who and scroll down the list looking for the database in question and killing connections at need. I don’t really like sp_who though. If I have several hundred connections, by the time I’ve reached the bottom someone else has gotten into my database and I have to start over. Not to mention the probability of me missing someone. So what’s a better solution? Well if you use sp_helptext on sp_who you will see that it uses the dbid column in sysprocesses. Unfortunately here is what we see right at the top of the help for sysprocesses:

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

And we follow the link for Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views we see that sysprocesses is mapped to sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests. As it happens these are three of my favorite DMOs. Database_Id is in sys.dm_exec_requests but that only helps if the session is actually doing something. In SQL 2012 and up you can find database_id in sys.dm_exec_sessions and this appears to map exactly to the dbid in sysprocesses. So quest solved right! Nope, 90% of the servers I’m working in currently are pre 2012. So now I’m in kind of a conundrum. I could write a query using sysprocesses, but I try to avoid using deprecated features when I can, or I can use sp_who, which I already complained about above.

Then I remembered sys.dm_tran_locks. This DMO displays information on locks currently held in the system. And yay us, being in a database is considered a lock! In fact it is a shared database lock. So now we write the query:

SELECT sys.databases.name DB_Name, request_session_id
FROM sys.dm_tran_locks
JOIN sys.databases
	ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type = 'DATABASE'
ORDER BY request_session_id

This is great! Except that it doesn’t show a shared database lock for master. Not sure why, but it doesn’t. Not really a big deal since chances are I’m never going to be doing anything to master that requires me to know who’s in it. I could go and look in sys.dm_exec_sessions for any session_ids that aren’t in sys.dm_tran_locks but then I’m going to get a number of sessions that, at least according to sysprocesses, belong to dbid 0. It probably doesn’t matter since they are all going to be system connections and again I don’t really need that information anyway. But just in case here is the code anyway.

SELECT sys.databases.name DB_Name, request_session_id
FROM sys.dm_tran_locks
JOIN sys.databases
	ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type = 'DATABASE'
UNION ALL
SELECT 'master' DB_Name, session_id
FROM sys.dm_exec_sessions
WHERE sys.dm_exec_sessions.session_id NOT IN (
		SELECT request_session_id
		FROM sys.dm_tran_locks
		WHERE resource_type = 'DATABASE')
ORDER BY request_session_id

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: