Changing Database In Single User Mode To Multi User

Question

Post reply

Changing Database In Single User Mode To Multi User

Rick Bellefond

Ten Centuries

Points: 1008
More actions
February 7, 2007 at 3:43 pm

#116115

I have a database RB_Data_Services_MSCRM that I put in a single user mode by
Alter database RB_Data_Services_MSCRM
set single_user with rollback immediate;
Now I cannot get it back into multi-user mode, delete it or rename it.
If I try enterring
Alter database RB_Data_Services_MSCRM
set single_user with rollback immediate;
I get a message saying:
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'rb_data_services_mscrm' 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.

Rick Bellefond

Viewing 14 posts - 1 through 13 (of 13 total)

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

Some one is connected that db...

Do you any monitoring tools connecting to this db...

What is the error you are getting...when you try change it to multi user mode?

Try the following script...

DECLARE @ID INT

DECLARE @MSG CHAR(8), @DataName sysname

While 1 = 1

BEGIN

Set RowCount 1

SELECT

@id = spid

FROM

Master..Sysprocesses P,

Master..Sysdatabases D

WHERE

<A href="mailto.Name=@DataName">D.Name=@DataName

AND

D.dbid = P.dbid

IF @@rowcount = 0

break

SELECT @msg = 'KILL ' + convert(char(8) ,@ID)

Print @msg

EXEC( @msg)

--break

END

GO

ALTER DATABASE....

GO

MohammedU
Microsoft SQL Server MVP

Rick Bellefond Ten Centuries Points: 1008 More actions · Answer 2

Mohammed,

When I try to put the db in multi user mode I get:

Msg 5064, Level 16, State 1, Line 1

Changes to the state or options of database 'rb_data_services_mscrm' 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.

Rick Bellefond

Rick Bellefond Ten Centuries Points: 1008 More actions · Answer 3

Mohammed when I tried your script I got the message saying

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-' -

Was I supposed to change @DataName to the name of my db?

Thanks

Rick Bellefond

SQLBill SSC Guru Points: 51440 More actions · Answer 4

To put it in multiuser mode you say:

If I try enterring

Alter database RB_Data_Services_MSCRM

set single_user with rollback immediate;

That is the code to put it in single_user mode, not multi_user mode. Try:

ALTER DATABASE RB_Data_Services_MSCRM

SET MULTI_USER

Also, make sure you ONLY have Query Analyzer open.

-SQLBill

MohammedU SSCoach Points: 19644 More actions · Answer 5

Yes... @DataName = your db name...

at the end of the procedure use your alter database statement...and make sure you use SET MULTI_USER option..

MohammedU
Microsoft SQL Server MVP

Rick Bellefond Ten Centuries Points: 1008 More actions · Answer 6

Mohammed,

Ok, I actually got it to go into multi user mode by going to Services and restarting the SQL Server and then giving it the

Alter database RB_Data_Services_MSCRM

Set multi_user;

command and then it took it.

I then wanted to rename it and so from the Management Studio I highlighted that db and then right clicked and selected rename. I gave it another name and then when I hit the enter key I got:

TITLE: Microsoft SQL Server Management Studio

------------------------------

Unable to rename RB_Data_Services_MSCRM22. (ObjectExplorer)

------------------------------

ADDITIONAL INFORMATION:

Rename failed for Database 'RB_Data_Services_MSCRM'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

****************************************

So how do I exclusively lock this data base so I can then rename or delete it?

Thanks.

Rick Bellefond

Rick Bellefond Ten Centuries Points: 1008 More actions · Answer 7

Bill,

Oops, I meant to post

ALTER DATABASE RB_Data_Services_MSCRM

SET MULTI_USER

Any idea as to how I can get exclusive control over this db so I can then rename it or delete it?

Thanks.

Rick

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

If you just want to be able to drop the database, use this:

use master
alter database RB_Data_Services_MSCRM set offline with rollback immediate

That will kick everyone out of the database, and set it offline so that no users can connect to it. Then you can use a drop database command to delete it.

Rick Bellefond Ten Centuries Points: 1008 More actions · Answer 9

Michael,

That is a good idea.

I will give that a try.

Thanks.

Rick Bellefond

MohammedU SSCoach Points: 19644 More actions · Answer 10

Again some one else in the db...

When you rename the database, sql will try to get the exclusive on the db so that it can rename it..

You can use my script to kill the connections or use ALTER DATABASE command...

immediately run the following...

EXEC sp_rename '<dbname>', '<New dbname>'

MohammedU
Microsoft SQL Server MVP

SQLBill SSC Guru Points: 51440 More actions · Answer 11

Exclusive mode pretty much means SINGLE_USER mode.

-SQLBill

Robert Davis One Orange Chip Points: 28027 More actions · Answer 12

Be aware that if you have Object Explorer open and focused on the database in question, that is a separate connection to the database. If you can find no other users connected to the database, then try closing object explorer.

Your best bet is to make sure that you maintain your connection to the database during the entire transaction.

Don't set it to single user, then do something else to it, then set it to multi user. Do the whole process in one transaction.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 13

If all you want to do is rename just run this:

alter database database name set single_user with rollback immediate

go

alter database DatabaseName modify name = NewDatabaseName

go

alter database NewDatabaseName set multi_user

go

print 'enjoy'