x

change to multiuser db mode

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
more ▼

asked Sep 11 '12 at 09:08 AM in Default

pits gravatar image

pits
830 73 91 92

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Sep 11 '12 at 03:39 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Sep 11 '12 at 11:16 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.5k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1

asked: Sep 11 '12 at 09:08 AM

Seen: 1348 times

Last Updated: Sep 11 '12 at 03:39 PM