I have had to set my database to single_user mode to allow for a dbcc checkdb repair and now I am unable to get my database back to multi_user. I have tried the following command in a query window against the master database but it hasn't worked as suggested by another Stack overflow post:
USE [master];
GO
ALTER DATABASE mydb SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
I get the following error:
Msg 5064, Level 16, State 1, Line 2 Changes to the state or options of database 'mydb' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed.
If I right click on the database and try selecting properties then it errors saying that it is already in use.
Any help would be greatly appreciated.
To get the session that was tied to the database I ran:
SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('mydb')
This yielded a processid of 55
I then ran:
kill 55 go
And I was the able to use the alter multi_user line that was previously not working