Get the connection back when your database is stuck in single mode

For some reason you put your database in SINGLE_MODE.


ALTER DATABASE fbDataCollector SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

After a while you realised that you can not bring it back in MULTY_USER because you have been locked out of your DB.

One option is to find the SPID who is using it, kill it and then bring it back to multy_user.

  use master
GO
— the following query lists all database sessions
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 = ‘DBNAME’
GO
— kill session using SPID number
kill 52
GO
exec sp_dboption ‘DBNAME’, ‘single user’, ‘FALSE’

GO

Bu, for some reason this option didnt worked today. After some time in agony, i find another option that actually worked, and it always works, altho it is not as fast as the first one.

The solution is simple. You will try to acces your database by bruteforce.



USE DB_NAME;

GO 1000
This will try to conect to your DB for 1000 times.
You will recive a lot of errors that your DB is in single mode, but after couple of hundreds you will suceed to get the session.
After you do, bring you database into MULTY_USER state with executing the following:

ALTER DATABASE DB_NAME SET MULTI_USER;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.