Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Changing Database In Single User Mode To Multi User Expand / Collapse
Author
Message
Posted Wednesday, February 7, 2007 3:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2008 12:18 PM
Points: 24, 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

 

Post #343275
Posted Wednesday, February 7, 2007 4:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:50 AM
Points: 1,190, Visits: 604

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
Post #343282
Posted Wednesday, February 7, 2007 4:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2008 12:18 PM
Points: 24, 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

Post #343286
Posted Wednesday, February 7, 2007 4:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2008 12:18 PM
Points: 24, 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

 

Post #343288
Posted Wednesday, February 7, 2007 4:36 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:47 PM
Points: 3,241, Visits: 538
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



Post #343290
Posted Wednesday, February 7, 2007 4:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:50 AM
Points: 1,190, Visits: 604

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
Post #343291
Posted Wednesday, February 7, 2007 4:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2008 12:18 PM
Points: 24, 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

Post #343296
Posted Wednesday, February 7, 2007 4:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2008 12:18 PM
Points: 24, 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

Post #343297
Posted Wednesday, February 7, 2007 8:05 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:09 PM
Points: 3,108, Visits: 11,502

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.

 

 

 

Post #343312
Posted Wednesday, February 7, 2007 8:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2008 12:18 PM
Points: 24, Visits: 13

Michael,

That is a good idea.

I will give that a try.

Thanks.

 

Rick Bellefond

 

 

Post #343314
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse