We tried to restore a database, and it failed for some reason. Now it is stuck in single user mode. Here is what I tried:
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 = 'mydb'
It returned one result and I killed it using
kill 77. I read somewhere that you should also kill the id plus one for good measure, so we also ran
kill 78. At this point, running the query again returned 0 connections.
I've tried all of these:
exec sp_dboption 'mydb', 'single user', 'FALSE'; SET DEADLOCK_PRIORITY HIGH exec sp_dboption 'mydb', 'single user', 'FALSE'; ALTER DATABASE [mydb] SET MULTI_USER WITH NO_WAIT ALTER DATABASE [mydb] SET MULTI_USER WITH ROLLBACK IMMEDIATE
They all give me
Msg 5064, Level 16, State 1, Line 1
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.
I realize that I could probably solve the issue by restarting the sql service or perhaps rebooting the server. However, this is a production server, so I'd prefer to avoid it.