Braindump of a session where I had to remove a SQL Server database from single-user mode.
1> exec sp_dboption 'testdb01', 'single user', 'FALSE'; 2> go Msg 5064, Level 16, State 1, Server REMIDIAN01, Line 1 Changes to the state or options of database 'testdb01' 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, Server REMIDIAN01, Line 1 ALTER DATABASE statement failed. sp_dboption command failed.
Now I can use TSQL below to find all the database sesions connected to this database.
TSQL script to retrieve client sessions per database.
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 = 'testdb01' go
Kill the particular session(s) with the ;kill’ command.
1> kill 51 2> go 1>
Now I can ‘remove’ the database from Sinlge user mode.
1> exec sp_dboption 'testdb01', 'single user', 'FALSE' 2> go 1>