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

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

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

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

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

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

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

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.

Michael,

That is a good idea.

I will give that a try.

Thanks.

Rick Bellefond