September 14, 2010 at 2:53 am
I took a DB in single user mode to repair some consistency error using dbcc checkdb using
alter database 'production' set single_user with rollback immediate
There after i am not not able to run any admin query like sp_who2 neither able to take the db in multi_user mode.
this is the error I get when I run any command
Msg 924, Level 14, State 1, Procedure sp_who2, Line 89
Database 'production' is already open and can only have one user at a time.
there after when i try to take in multiuser mode using command
alter database 'production' set multi_user
this return following error
Msg 1205, Level 13, State 68, Line 1
Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I am not able to see any processes which cn be culprit.I am stuck and business is getting hamper.
Can someone help me to bring the db in multi user mode
September 14, 2010 at 3:01 am
HI Sandeep,
Have you tried to set it multi user.
If not.
try this
ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT
September 14, 2010 at 3:11 am
Yes Gopi I already tried that but received the following error message
Msg 1205, Level 13, State 68, Line 1
Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Any other ways?
September 14, 2010 at 6:19 am
There is some other process active on the database. You need to clear that connection before bringing the database into MULTI_USER mode.
I had discussed a similar issue here[/url]
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 14, 2010 at 10:02 am
Try to kill the connections accessing the db and then try to bring the db to multiuser mode.
If a particular login is reconnecting to the db even after killing it, try to disable that login and then do the needful.
In single user mode only SQL server acepts only one connection. So close all the windows in the SSMS. Even if you expand a node in the SSMS it will be treated as a connection.
If nothing else works, you can even try to detach the db and then reattach it.
Thank You,
Best Regards,
SQLBuddy
September 14, 2010 at 10:30 pm
If any service is connecting to your database on timely manner than stop that service. Because your database is currently in Single user mode, if that service is connecting the database, no other connection is able to connect the database.
If so, then stop the service and execute the below command:
ALTER DATABASE <dbname> set MULTI_USER
Thanks
September 14, 2010 at 10:43 pm
Hardy21 (9/14/2010)
If any service is connecting to your database on timely manner than stop that service. Because your database is currently in Single user mode, if that service is connecting the database, no other connection is able to connect the database.If so, then stop the service and execute the below command:
ALTER DATABASE <dbname> set MULTI_USER
An example of such a service is the SQL Server Agent 🙂
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
March 10, 2011 at 5:12 am
I had the same issue trying to enable service broker. After putting db in single user mode there is still API process holding connection and would not allow me to put db back into multi-user mode or enable service broker.
Killing connection would not work as would instantly reconnect so I disabled login for this process as suggested, then killed connection and then it would allow me to proceed.
Thanks!
March 10, 2011 at 10:03 am
Have you tried using the ROLLBACK IMMEDIATE when setting it to MULTI_USER?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply