Msg 1205, Level 13, State 68, Line 10 Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Msg 5069, Level 16, State 1, Line 10 ALTER DATABASE statement failed.
On a couple of occasions I have got caught in a situation where I ALTER the database to single_user mode and some other process takes a connection to the database. After this I can not change the database to multi_user mode.
It is possible to look at sp_who2 or sysprocesses and find the user SPID ( > 50) that is connected to the database and then kill it.
But sometimes I have found that the SPID is less than 50. Meaning it is a system process and you can not kill a system process. You will get the following error when you try to do that.
Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.
This means that there is a background system process that is connected to the database. It is possible that database has AUTO_UPDATE_STATISTICS_ASYNC option set to ON and the stats might be getting updated. This could be potential cause of the problem. To verify this check the DMV sys.dm_exec_background_job_queue
SELECT * FROM sys.dm_exec_background_job_queue
You can grab the JOB_ID from the result set and then use it in the command KILL STATS JOB to stop the process.
KILL STATS JOB 23
Apart from this the only other way to get the database in multi_user mode would be to stop SQL Server and then start the instance in single user mode and then changing the database to multi_user mode.
To avoid this situation just turn off the AUTO UPDATE STATISTICS configuration of the database before changing it to SINGLE USER mode.
USE [master]
GO
ALTER DATABASE [AdventureWorksDW_V2] SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT
GO
ALTER DATABASE [AdventureWorksDW_V2] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT
GO
ALTER DATABASE [AdventureWorksDW_V2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT
GO
