Solved

Urgent!.  Error 22285:SQL-DMO Database 'MyDatabase'; already open and can only be open by one user

Posted on 2006-11-28
11
1,116 Views
Last Modified: 2008-01-09
I have SQL 7 database that all of a sudden has a mark next to the name (if you are looking in EM) as 'single user'.
When I try to open any of the table, I am getting the error:
Error 22285:SQL-DMO Database 'MyDatabase'; already open and can only be open by one user

How would I resolve this?
0
Comment
Question by:novice12
  • 8
  • 3
11 Comments
 

Author Comment

by:novice12
ID: 18028029
I found this on the web, that I am going to try:

ALTER DATABASE Mydatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE

What do you think?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18028097
That's how we change to multi user ,
0
 

Author Comment

by:novice12
ID: 18028409
That did not work - It keeps coming back with:
The database is already open....can not open it
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:novice12
ID: 18028468
I am sorry I forgot to mention that this is a SQL 7 database. I did not use the query above, I used a SQL 7
I think it was : set db_option 'single user' , 'false'
but that won't work, because the databse is in single user mode, and it will only allow one conetion. Currently I am disconnecting the server from the network, and rebooting....
0
 

Author Comment

by:novice12
ID: 18028726
Ok, after disconnecting the server form the network. I still wasn't able to do:
Exec sp_dboption VisitorReg, 'single user', 'false'
When I wen to database property in EM, I was able to un-check single user. And it worked!

I just don't know how did the database ended up being in single user to begin with. Can the database automatically go into 'single user'. It must be a SQL batch doing it, maybe?

Thanks

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 18028850
Was there any maintenace plans running /
0
 

Author Comment

by:novice12
ID: 18029042
I am showing that one job has failed, the message is as follows:
Integrity Check Job for VisitorReg has failed.

The maintanance plan that executes this job, is set to write to the windows event-viewer only. The event viewer size however is too small. So at this point I know that this job has failed, I just don't know why?
0
 

Author Comment

by:novice12
ID: 18029144
I looked under SQL server Logs and I saw logs of the above maintanance plan executing. I did not see any errors logged however.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18029163
is it still executing ?
0
 

Author Comment

by:novice12
ID: 18029183
I have SQL 7 , sp 2.
There is an article in MSDN about this problem.
http://support.microsoft.com/default.aspx?scid=kb;en-us;276234


0
 

Author Comment

by:novice12
ID: 18029491
Yes it's running fine now. I am going to monitor it, to see if the problem comes back tomorrow.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

679 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