Database stuck in Single_user mode

Today I got a call from a colleague who was trying to restore a database, and as the generic first step had put the database in single_user mode and after that ran the Restore query which somehow failed.

Now the issue began, this database which was put on single_user mode was not coming back to multi_user, and was getting the “database is in single-user mode, and a user is currently connected to it” error.

This is a normal occurrence and it happens because when you put the database in single_user mode and somehow a new connection comes in between the end of putting the database is single user mode and starting your restore query.

As a normal step I checked the sp_who2, but there was no session using this database.

Checked the sys.dm_exec_requests and sys.dm_exec_sessions but nothing related to this database in there.

Now I was confused, when we run Alter Database command SQL Server basically checks if there is any lock onto the Database. So knowing this I ran the below query. GitHub link.

SELECT request_session_id 
FROM sys.dm_tran_locks 
WHERE resource_database_id=DB_ID('Database_in_Q');

From the above query I was able to find the session which had kept a lock on the database and so was not letting me change the user mode. Once that was killed I was able to put the database back in multi_user.

I hope it helps!