Solved

Locked out of my SQL Server.

Posted on 2007-04-09
3
732 Views
Last Modified: 2012-06-27
I have MSSQL Server running on a Windows Server 2003 environment.  The main DB we use to run our entire business went to single user mode for some reason.  The fix should be simple, just switch back to multi-user mode.  However...when I try to do anything (view tables, users, go to properties) I get the following message:

Error 22285: [SQL-DMO]Database 'GM001' is already open and can only have one user at a time.

This is problematic since I need to get to properties to change back to multi-user mode.
0
Comment
Question by:magicspringsadmin
3 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 18876561
You need to kill the process that is connected to it.

Open Query Analyzer & point to a different DB (master?).  type "Exec sp_who2" and press F5.

Scan the results to find the SPID that is using the DB in question -- GM001, I guess.

Then execute sp_kill using that SPID as a parameter.

After that, you should be able to go into the DB properties & take it out of single-user mode.
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18876586
This would indicate that there is already a open.

Here is a simple way to fix this.

Open Query Analyzer and enter these commands:

use GM001
go
sp_dboption 'single user', false
go

Do not execute it

Now in Enterprise Manager - right click on the database
Choose All Tasks
Detach database
Click the clear button to close all connections

Now run the query in Query Analuzer.

There should be no time lag between clicking the Clear button and running the query

0
 

Author Comment

by:magicspringsadmin
ID: 18876792
Well, I tried that, but I have software running on about 42 computers constantly trying to re-establish the connection.  I went around and shut down the software on all of them and now everything works just fine.  Thanks.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now