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

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, DMV, language sql, locks, microsoft sql server, sql statements, system functions, T-SQL