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, 2012 at 09:08 AM in Default

avatar image

pits
830 89 92 95

(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, 2012 at 03:39 PM

avatar image

Usman Butt
14k 6 13 21

(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, 2012 at 11:16 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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.

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, 2012 at 09:08 AM

Seen: 1876 times

Last Updated: Sep 11, 2012 at 03:39 PM

Copyright 2016 Redgate Software. Privacy Policy