Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 SQL Server 2005 Getting out of single user mode

Author  Topic 

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-06-30 : 09:55:38
Hey I have a database stuck in single user mode and cannot for the life of me get back to multi-user.

I tried the following

Alter Database MyDatabase Set Multi_User

But get

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'MyDatabase' 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 tried SP_WHO, to try find the ID to kill

Msg 924, Level 14, State 1, Procedure sp_who, Line 79
Database 'MyDatabase' is already open and can only have one user at a time.

Any help would really be appreciated

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 10:19:16
use master
select * from sysprocesses where dbid = db_id('MyDatabase')
kill ...
select * from sysprocesses where dbid = db_id('MyDatabase')
-- repeat until no resultset
alter ...


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-06-30 : 10:33:04
when running
select * from sysprocesses where dbid = 238

I get

Msg 924, Level 14, State 1, Line 1
Database 'MyDatabase' is already open and can only have one user at a time.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 10:48:07
did you run the
use master
statement?

It sounds like your default database is the database you want to affect so you can't get a connection.
Make sure that the object browser window in management studion is pointing at a different database.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-06-30 : 10:57:53
Yup I ran
use master
select * from sysprocesses where dbid = 238

the database im trying to change is not the default
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-30 : 11:14:59
[code]
use master;
go

alter database MyDatabase set multi_user with rollback immediate;
GO[/code]
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 11:17:35
Can you run
select 1
Probably as you can run the use command.

238 won't be a system database so I don't know why it's complaining about querying sysprocesses.
I take it you can't do
select * from sysprocesses
either

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-06-30 : 11:34:56
@Russel

I get

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'MyDatabase' 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.

@nigel

running

select * from sysprocesses

gives me

Msg 924, Level 14, State 1, Line 1
Database 'MyDatabase' is already open and can only have one user at a time.

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 11:36:18
What about
select 1
?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 11:42:00
I'm tempted to suggest bouncing the instance as this doesn't sound right.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-30 : 11:45:05
I agree with Nigel.

By the way, you do have ALTER DATABASE or SYSADMIN permissions, right?
Go to Top of Page

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-06-30 : 11:47:30
select 1 runs fine just returns "1"

well i managed to alter database to single user (If I could only roll back biggest mistake I ever did, if i knew this would end up being a nightmare) so I guess so
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 11:53:56
How about
select * from sysservers



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-06-30 : 12:00:15
thats cool to runs fine
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 12:08:03
Try
select * from sysprocesses
again
and also
select session_id from sys.dm_exec_connections

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-06-30 : 12:20:35
select * from sysprocesses
seems to run and returns a lot of results but then switches to
Msg 924, Level 14, State 1, Line 1
Database 'MyDatabase' is already open and can only have one user at a time.
Says at the bottom query complete but with errors

select session_id from sys.dm_exec_connections
runs fine

Go to Top of Page

revdnrdy
Posting Yak Master

220 Posts

Posted - 2011-06-30 : 12:37:33
Hello;

On another forum the person was also getting your error. Here is his post in case you are interested.

I am having a similar problem where someone managed to put a databse into single user mode. Running sp_who and sp_who2 tell me the database is already open and can only have one user at a time. Trying to go into Activity Monitor gives me the same problem.

RESOLVED : I had to use Dedicated Admin Connection to take the database back to multi user mode

Here is the forum post link:
http://www.sqlservercentral.com/Forums/Topic332162-146-1.aspx

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 12:44:33
select spid, dbid from sysprocesses


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -