Solved

Locked out of my SQL Server.

Posted on 2007-04-09
3
743 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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