|
how to change database in multiuser mode in sql 2008 r2? we already tried "ALTER DATABASE MALI SET MULTI_USER" , "ALTER DATABASE MALI SET ONLINE" , GO ALTER DATABASE MALI SET MULTI_USER WITH ROLLBACK IMMEDIATE GO but received below error , the db is still in sigle user mode. Msg 1200, Level 14, State 69, Line 1 Transaction (Process ID 135) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Msg 8069, Level 76, State , Line 1 ALTER DATABASE statement failed. Also I am not able to right click db and get in to the properties....received error message that db is already open and in single user mode!! There is not any opentransaction exists... we also tried sp_dboption 'db_Name', 'single user', 'FALSE' GO ALTER DATABASE MALI SET MULTI_USER WITH NO_WAIT GO But no use...same deadlock error but no open transactions observed? can you please help? thank you
(comments are locked)
|
|
This could happen if AUTO_UPDATE_STATISTICS_ASYNC option is set to ON. When set to ON, the background thread used to update statistics takes a connection against the database, and one will be unable to access the database in single-user mode. So, one needs to check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dmv and if there is any job running then either let it complete OR manually terminate it by using KILL STATS JOB
(comments are locked)
|
|
It sounds like you have a connection open and a transaction open on that database. Can you see any connections on the database? If so, what state are they in? Use sys.dm_exec_requests and sys.dm_exec_sessions. Between these two you should be able to see the connection and see what it's doing. Join to sys.dm_exec_sql_text using the statement start and end offsets from sys.dm_exec_requests to see exactly what statement is currently being executed. I would try killing that connection, if possible. If not, you may be in a situation where you need to reboot. But I would thoroughly examine that open process first.
(comments are locked)
|
