?
Solved

Database being left in single user mode.

Posted on 2006-05-21
15
Medium Priority
?
2,111 Views
Last Modified: 2008-01-09

If any expert is on line here on sunday, then, please reply urgently. I have got to set things right as soon as possible.

Currently I have been stuck in single user mode.

When I run
use master
exec sp_dboption 'siddiqsons','single user ', 'false'
it says
The database is in single-user-mode and a user is currently connected to it.

This message is not understandable. As, I am using the server machine itself and I have disabled the lan.
0
Comment
Question by:Mateen
  • 7
  • 5
  • 3
15 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 total points
ID: 16727738
Hi Mateen,
Run the following in QA

 
    ALTER DATABASE mydb
    SET MULTI_USER

Aneesh R!
0
 

Author Comment

by:Mateen
ID: 16727749
I have restarted the server,

Please remain with me.

I would need your help a lot.

Thank that u r here.

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16727755
ok.. i will be online
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Mateen
ID: 16727803
Hi ,
I tried
use master
go
alter database siddiqsons set multi_user

"Changes to the state or options of database 'siddiqsons' cannot be made at this time. The database is in single-user mode , a user is currently connected to it.


As a matter of fact, I have got siddiqsons corrupted ( so that even deletion / restore from tomorrows night backup has also become impossible for me

Currently it is as
siddiqsons(Read-only\Offline\Emergency Mode)

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16727810
   ALTER DATABASE  siddiqsons
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE


    ALTER DATABASE siddiqsons
    SET MULTI_USER
0
 

Author Comment

by:Mateen
ID: 16727820
tried
 ALTER DATABASE  siddiqsons
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

exactly the same error message.

If this information helps u.
Siddiqsons is being used in replication.
0
 

Author Comment

by:Mateen
ID: 16727835
Hi ,

I am prepared to delete the database 'siddiqsons' and then restore from the last night backup.

When I delete,
"Cannot drop the database, because it is being used for replication"

When I try to disable publishing

Error 924: Database siddiqsons is already open and can only have one user at a time

I AM STUCK
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16727841
use master
go
exec sp_removedbreplication 'siddiqsons'
go
drop database 'siddiqsons'
0
 

Author Comment

by:Mateen
ID: 16727857
use master
go
exec sp_removedbreplication 'siddiqsons'
go
drop database 'siddiqsons'


Error:
Database siddiqsons is already open and can only have one user at a time

We have got to make it multi user first, as whatever command I execute it says about being single usability.

0
 

Author Comment

by:Mateen
ID: 16727866
I am unable to drop publishers
another error mesage

Could not run Begin Transaction in database 'siddiqsons' because the database is in bypass recovery mode
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16727891
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16727903
hi there,

Did your try dropping the publication from the enterpise manager on the server.
0
 

Author Comment

by:Mateen
ID: 16727914
Hi Aneesh R!

I cannot explain, how in the world , I succeeded to delete the database siddiqsons.

I do want to make a point.

The name was showing like this

siddiqsons(readonly,offline,emergencymode)     AND NOT SINGLE USER MODE

I right clikced it and selected the only option available make it offline.
After this
siddiqsons(readonly,offline,emergencymode)    
become

siddiqsons(singleuser,offline,emergencymode)    
and then the database was deleted.

Currently in the process of restoring the backup set.



0
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 800 total points
ID: 16727935
/*

    This is what you have to do if you want to chage the database from single user to multi user.

*/
update sysdatabases set status = 16 where name ='test'
0
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 800 total points
ID: 16727939
you should enable allow update on system tables first


exec sp_configure 'allow updates', 1
reconfigure
go


update sysdatabases set status = 16 where name ='test'
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 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