Sometimes an SQL server database changes its state to single-user mode to prevent corruption of database tables.
To bring to back to online, use the below command via SQL Management Studio:
exec sp_dboption 'database', 'single user', 'FALSE'
Execute
If any user is still accessing the database (via codes or via SQL Management, it will show the below error:
Changes to the state or options of database 'database' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
So, now you need to find which user is connected to it and the Server Process ID. Use the below command for that:
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid where d.name = 'database'
Execute
The above command will list a table with SPID column too. You can now kill the Server PID to terminate that user's connection/session to the database:
kill $spid
Execute
Now set the database back to multi-user mode.
ALTER DATABASE database SET MULTI_USER
Execute
OR
exec sp_dboption 'database', 'single user', 'FALSE'
Execute
That should do the trick.
------------
Thanks.
