Cannot access database in single user mode

SQL Server database can be changed to single user mode by the below command.

ALTER DATABASE SET SINGLE_USER
GO

Keep the session from where you ran the ALTER DATABASE alive and open a new session.
Try to use the database from the new session and it throws the below error.

Error 924 - The database is already open and can only have one user at a time.

When you run the below query to find out any session using this database context it will return zero results.

SELECT spid, DB_NAME(dbid) FROM sys.sysprocesses WHERE DB_NAME = 'mydb'
GO

But when you run the below query you will see the locks on the database from the session which altered the database to single user mode.

SELECT * FROM sys.dm_tran_locks WHERE DB_ID('mydb') = resource_database_id
GO

So you will be able to use the database in single user mode from the session where you ran the ALTER statement or close that session and then try to use it from other session.

This is by design of SQL Server. The whole intention of holding locks on the database after altering the database to single user mode even though the database is not associated with any session is very simple. The session which changed the database to single user mode should have the capability to change it back to multi user. No other session should change back the database to multi user while this session is doing some operations on the database in single user mode.

So now you know, use a single session while you are working with single user database. If the session is orphaned the locks remain and you will never be able to get back the database to multi-user. The only solution then is to stop the services, delete the log file and start the services. Drop the database and restore from the most recent backup available which is not at all recommended as a DBA. Prevention is better than data loss and downtime.

Happy Troubleshooting,
Manu