Current database vs database locks

I have a lot of co-workers who use sp_who and sp_who2 to see who’s in a given database. This is typically when they want to alter a database and need to get everyone out.

Two quick notes: If there are any locks of any type in a database then you can’t get the type of access (exclusive) needed to modify a database setting. And one of the output columns for sp_who and sp_who2 is dbname which is the current database.

Using sp_who and sp_who2 works .. usually. But there is an occasional problem. Because they are looking at the current database not database locks.

Current database

What does the current database mean? You set the current database when you use the USE command, the dropdown in the top left-hand corner of SSMS, specify an initial database when you connect to SQL or, last but not least, the default database associated with your login, again when initially making a connection. From what I’ve seen it has two effects.

1. There is a shared database schema lock. (Unless the context is master or tempdb.)

   This is why a lot of people will use sp_who and sp_who2 to check for problems when they want to get exclusive access to a database (to do an ALTER or something). Don’t worry though, it won’t have any effect 99% of the time, unless you are doing one those ALTERs.

2. You don’t need to use a three part name. SQL assumes you mean the current database if you don’t specify otherwise.

How do I tell what the current database is? As I’ve said, a lot of people I know use sp_who and sp_who2. Not my favorite technique. Unless you dump the output into a temp table you can’t query it. You just have to skim through the output. Not a big deal when you have 10 or 20 connections. When you have a few hundred it becomes a bit more annoying, and if you have databases with very similar names it becomes even harder. So what’s my preference? Well, if you want to tell your own current database you can look in the top left hand corner of SSMS, or programmatically you can use DB_ID or DB_NAME and if you want to see everyone’s you can query the database_id column of sys.dm_exec_sessions. This way you can write a query like this to get everyone who’s database context is a specific database.

Much easier than sp_who right? But if we are trying to get everyone out of a database, this still isn’t really what we want to know. We want

Database Locks

What’s the difference? Well, any time need a lock in a database, one of the locks you’re going to get is a database lock. So what happens when you have a transaction that hits more than one database? For example, if I have this query in one session:

And then I run this in another session:

SELECT DB_NAME(resource_database_id), request_session_id,  
resource_type, request_mode, request_type 
FROM sys.dm_tran_locks
WHERE resource_type = 'DATABASE'
ORDER BY request_session_id;

You can see that my previous session (session_id = 53) has locks in more than one database. It actually has more than just those two locks but for these purposes, I only care about the DATABASE locks. You’ll also see that spid 54 has a database lock and my current session (55) doesn’t have any database locks.

Conclusion

There are a few things you should get out of this.

• sp_who and sp_who2 .. well .. I’m not going to say are terrable, but less than super useful.
• Database context is useful to know for your connection but probably not for someone else’s.
• You probably want database locks, which is something else entirly.