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