Need to set the DB in to Multi-User mode

Question

Need to set the DB in to Multi-User mode

vamshi.sql

SSCommitted

Points: 1770
More actions
June 22, 2010 at 8:29 pm

#222528

I have a database son_db_train that I put in a single user mode by
Alter database son_db_train
set single_user with no wait;
Now I cannot get it back into multi-user mode, delete it.
If I try enterring
Alter database son_db_train
set multi_user with no wait;
I get a message saying:
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database son_db_train' 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, Line 1
ALTER DATABASE statement failed.
I am the only user on the system so I want to get this db in a state where I
can either just rename it or delete it.
Any suggestions on how to do that?
Thanks for all of your help.I need answer for this as this is a sev1 issue.Please help me IMMD.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

henners72 SSC Eights! Points: 934 More actions · Answer 1

You may be the only user on the system, but that doesn't mean there is not another connection to the dataabse. Confirm in activity monitor there are no active connections. If there are kill them and then create a connection to the database.

Failing that Restore the database with another nams and see if you can set that to multi user, if so then you could simply rename it, or back up and restore ove thte original.

vamshi.sql SSCommitted Points: 1770 More actions · Answer 2

I am not able to set the DB to Multi_User mode.I have the backup file with me,If I want to delete the DB also I am unable to do it.Getting the error (Dead Lock).I have seen in the activity monitor,there are no users connected to DB.Still I am unable to do set the DB in to multi_user

Atleast not able to keep the DB offline.

Minaz Amin SSChampion Points: 11052 More actions · Answer 3

vamshi.sql (6/22/2010)
I am not able to set the DB to Multi_User mode.I have the backup file with me,If I want to delete the DB also I am unable to do it.Getting the error (Dead Lock).I have seen in the activity monitor,there are no users connected to DB.Still I am unable to do set the DB in to multi_user
Atleast not able to keep the DB offline.

Dont panic, just relaxed :-), you have the DB backup. First thing, are you trying to bring the DB to muti user mode from management studio? If yes then please close that connection, in fact close the management studio and open the new query window and select master DB. Now type in the command Alter database command to bring the DB to multi user mode.

If this fails, close all the windows and management studion reopen the management studio once again, this will initialize all the connection and Restore the DB.

Let me know if this helped...:-)

"More Green More Oxygen !! Plant a tree today"

henners72 SSC Eights! Points: 934 More actions · Answer 4

Try using a DMV to see what's happening, start with sys.dm_tran_locks, there are some good articles out there. I agree that the previous post will most likely resolve the issue, but it is good practice to know what's causing it.