SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing Database In Single User Mode To Multi User


Changing Database In Single User Mode To Multi User

Author
Message
Rick Bellefond
Rick Bellefond
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 13

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


MohammedU
MohammedU
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4790 Visits: 812

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
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
Rick Bellefond
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 13

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
Rick Bellefond
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 13

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
SQLBill
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11737 Visits: 1085
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
MohammedU
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4790 Visits: 812

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
Rick Bellefond
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 13

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
Rick Bellefond
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 13

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
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14653 Visits: 11848

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
Rick Bellefond
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 13

Michael,

That is a good idea.

I will give that a try.

Thanks.

Rick Bellefond


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search