Admin-Ahead Community

Windows => General Windows => Topic started by: lathu_official on January 18, 2014, 01:48:48 pm

Title: How to remove SQL Server database from single-user mode
Post by: lathu_official on January 18, 2014, 01:48:48 pm

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.