How to get access from a locked out single user database.

Problem: How to get access from a locked out single user database.

Solution: Set a Database to Multi-user Mode

Sometimes the database gets locked out to a single user and the session gets killed and which makes the database not accessible for other users.

  

In that case, we have to turn the database back to multi user mode by killing the sessions that are acquired to the database using master database.

Example:

In this example, I would like to show how we can get over a single user database to multi user.

Step 1: get the list of sessions running on the database.

use master

 go

sp_who2

Results:

SPID	Status	BlkBy	DBName	Command	CPUTime	DiskIO	LastBatch
58	sleeping	.	DB	AWAITING COMMAND	0	0	07/07 15:56:14
59	RUNNABLE	.	DB	SELECT	32	0	07/07 16:11:12

Step 2: Kill the sessions that associated to the database

KILL 58

KILL 59

Step 3: set the database to multi user.

use DB

 go

ALTER Database DB SET MULTI_USER