Test Before Attempting Exclusive Lock

Question

I've written some code to upgrade a SQL Server database. Before I upgrade the database, I attain an exclusive lock via:

ALTER DATABASE Test SET SINGLE_USER WITH NO_WAIT

However, I'd like to test the database to see if the exclusive lock is possible before I run the above code. The test doesn't have to be 100% perfect, I'd just like to avoid the possibility of a timeout when attempting to gain an exclusive lock.

To that end, I've written the code below:

SELECT 
    *
FROM 
    sys.db_tran_locks 
WHERE 
    resource_database_id = DB_ID('Test') AND
    request_session_id <> @@SPID

I'm assuming that if there's 1 or more row returned, then the database must be in use. Is this true? Or is it not that simple?

UPDATE Taking @gbn's comments into account, I've decided to force rollback of existing connections using the following statement:

ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Before running this code, I'll give the user the an opportunity to opt out. However, I'd like the user to be able to see the list of active connections to the database - so they can make an informed decision. Which leads me onto this question.

Answer 1

What if the database becomes busy from the time you determined that it was not in use, until the point where you try to acquire the exclusive lock?

Answer 2

Mainly, a DB lock is just to show it is in use. Databases don't really have many exclusive locks situations compared to code/table objects.

Single user mode is not a lock, but the number of connections allowed.

I'd wrap the ALTER DATABASE in a TRY/CATCH block because there is no guarantee the state won't change between check and ALTER DB.

However, I could be wrong or misunderstanding the question... so you'll also have to test for the exclusive lock mode on the database resource in the query above. Your code above will show you any lock, which could be someone having a blank query window open in SSMS...

Edit, based on comment

You can detect who is using it by these:

• sys.dm_exec_connections
• sys.dm_exec_sessions
• sys.dm_exec_requests

To be honest, it's difficult to stop auto stats update or a user taking the single connection. Normally, you'd this which disconnects all other users and not bother waiting...

ALTER DATABASE MYDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE