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!