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 followingAlter Database MyDatabase Set Multi_UserBut getMsg 5064, Level 16, State 1, Line 1Changes 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 1ALTER DATABASE statement failed.I tried SP_WHO, to try find the ID to killMsg 924, Level 14, State 1, Procedure sp_who, Line 79Database '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 masterselect * from sysprocesses where dbid = db_id('MyDatabase')kill ...select * from sysprocesses where dbid = db_id('MyDatabase')-- repeat until no resultsetalter ...==========================================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 runningselect * from sysprocesses where dbid = 238I getMsg 924, Level 14, State 1, Line 1Database '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 masterstatement?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 ranuse masterselect * from sysprocesses where dbid = 238the 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;goalter 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 runselect 1Probably 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 sysprocesseseither==========================================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
|
@RusselI getMsg 5064, Level 16, State 1, Line 1Changes 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 1ALTER DATABASE statement failed.@nigelrunningselect * from sysprocessesgives meMsg 924, Level 14, State 1, Line 1Database '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 aboutselect 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
|
Tryselect * from sysprocesses againand alsoselect 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 toMsg 924, Level 14, State 1, Line 1Database 'MyDatabase' is already open and can only have one user at a time.Says at the bottom query complete but with errorsselect session_id from sys.dm_exec_connectionsruns 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 modeHere 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. |
 |
|
|