SQL Server 2005 Getting out of single user mode

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.

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.

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.

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

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]

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.

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.

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.

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.

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?

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

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.

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-06-30 : 12:00:15

thats cool to runs fine

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.

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

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources