March 4, 2015 at 10:54 am
I have tried everything I found on Google, but nothing seems to be working.
Already Ran alter command set multi_user, but getting an error.
"Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 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."
Any help will be highly appreciated.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 4, 2015 at 11:04 am
Do an sp_who2 and see where the connection is coming from.
Also make sure if you are doing this from SSMS that your open query is not connected to that db when you try to alter it.
March 4, 2015 at 11:07 am
1. Find out from sys.sysprocesses who is connected to that database.
2. Kill his SPID.
3. Alter database <> set multi_user .
All these assumes that you are member of sysadmin role
March 4, 2015 at 11:10 am
New Born DBA (3/4/2015)
I have tried everything I found on Google, but nothing seems to be working.Already Ran alter command set multi_user, but getting an error.
"Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 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."
Any help will be highly appreciated.
run this to find what's the spid
SELECT sd.[name], sp.spid, sp.login_time, sp.loginame
FROM sysprocesses sp
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid
WHERE sd.[name] = 'MyDatabase'
Then run this
KILL [spid number here]
GO
SET DEADLOCK_PRIORITY HIGH
GO
ALTER DATABASE MyDatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
March 4, 2015 at 11:20 am
I'm not sure, but with a database in SINGLE_USER mode, I think it's possible for a non-sysadmin user to login and block even the DBA, or the DBA may be able to block themselves with another query window open. That's why I set database to RESTRICTED_USER, which would support an unlimited number of DBA connections while denying access to the small folk.
Try this:
ALTER DATABASE database-name SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 4, 2015 at 11:41 am
Followed everything in this thread recommended by everyone, but nothing seems to be working.
Ran this command which brought 0 result
SELECT sd.[name], sp.spid, sp.login_time, sp.loginame
FROM sysprocesses sp
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid
WHERE sd.[name] = 'dpa_repository'
Followed SQL-Lover and Eric's suggestion, but didn't get anywhere with that. Getting the same error
"Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 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
1 more piece of information I 'd like to add if it helps. I am trying to DROP this DB
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 4, 2015 at 11:50 am
You may have multiple 'connections' open to the database. One that trips users up is to expand the list of databases, click on the database (one connection), and then open a query window (second connection).
I suggest closing completely out of SSMS, reopening it, do not expand the databases, just click on New Query. Then run your query from Master. Or by adding USE <databasename> switch the query to the database. But do not click on the database name in the expanded list and don't open a second query window.
-SQLBill
March 4, 2015 at 11:59 am
This is what I did.
I logged in as "SA"
Ran this command
SELECT sd.[name], sp.spid, sp.login_time, sp.loginame
FROM sysprocesses sp
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid
WHERE sd.[name] = 'dpa_repository'
got the SPID = 55
Ran KILL 55
Command completed successfully
Then ran Alter database set multi_user with rollback immediate:
Worked out pretty good. Thanks Everyone
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 4, 2015 at 12:02 pm
Run both views from sys. schemas.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply