Solved

single user mode in GUI

Posted on 2010-08-20
12
496 Views
Last Modified: 2012-05-10
when the DB is in single user mode, is there any way for that single user to be in GUI. When there is no user connected to that database

select count(*) from sys.sysprocesses where dbid = 19 is 0

still when i right click on that database name in SSMS, it says another user is using it or at other times, it says the database is not accessible.

is that a limitation?
0
Comment
Question by:anushahanna
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 100 total points
ID: 33489094
I'm not sure why that would be an issue, if NOTHING else is connected to that db.

For example, in SSMS, if you did this:

USE master

ALTER DATABASE testdb
    SET SINGLE_USER

--verified that no one else was using testdb

I would think you would be able to use it exclusively through SSMS.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33489831
yes, i even did

use master
select * from sys.sysprocesses where dbid = 19

to make sure there is no processes.. still SSMS will not allow me to touch the DB.

are you able to?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33532473
could this a version issue? I am using standard 2005 SSMS.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33589190
i tried this in enterprise 2005 also, but same issue.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33589959
Sorry, i don't know what else to do here.

You can cancel this q and try it again, maybe s/o will come up with an answer.
0
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 200 total points
ID: 33590510
I started the profiler while I click a single user db in SSMS (and  I get the same error as you).

I didn't study the entire flow of sql captured by the profiler, but near the beginning it is looking at master.sys.databases.user_access (which indicates the db is in single_user).  You could study the output further to see what it does with the result, but I'd guess for some reason they specifically test this before connecting from the gui.
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 6

Author Comment

by:anushahanna
ID: 33599585
OK. thanks.

so apparently, even though GUI is considered as a SQL process (i verified it in multi user mode), it is not allowed in single user mode, apparently SQL Server wants to prioritize it for SSMS window queries? do you think that is a possible reason for such design?
0
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 200 total points
ID: 33601101
Not having any special insight but that would be a reasonable guess.  We'll have jobs fail sometimes because somebody accidentally clicked a database and left SSMS running (preventing a maintenance job from getting exclusive access), so I know it is easy to do.  My other thought would be that you generally put a db into single user mode so you can take a specific action and then you put it back to multi user.  I can't think of the full list of reasons but I suspect few if any of those actions would or could be initiated from the gui.
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 200 total points
ID: 33602061
I had a DB & vendor app that was a semi-web based app with a "service" that would check in on a regular basis. I also had a nightly job that run a "dbcc checkdb". Every couple of days we would come in and the dB was in single user mode because the service app was hitting the DB before the dbcc/SQL Agent could put it's hook in.

I developed a procedure with the sp_who2 to find users of the DB, kill them, then let the dbcc run its course. It worked well. I think it is on EE.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33636565
jimpen, why did that app need to be in single user mode?

i search "jimpen sp_who2" came up with 8 entries including this, but not sure i saw a code snippet. do you kill it in a cursor?
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 200 total points
ID: 33651494
The DBCC CHECKDB needs to have the database in single user mode. The app doesn't care. But what would happen is that the SQL Server agent would put the DB in single user mode so it could run the checkdb. In the time frame between being put into single user mode (or because the App user ID was already logged in) and the checkdb starting the agent wasn't able to run the scan.

As far as the code -- it was basically

insert into #temptable
sp_runsql ' sp_WHO2'

DELETE from #temptable where database not in ('mydbname', mydbname2', ...)

-- Then a cursor type thing to

sp_runsql 'kill ' + @procid
fetch next
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33661961
sounds good. thanks for explaining that, Jim!

Enjoy your very bright day :-)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

867 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

22 Experts available now in Live!

Get 1:1 Help Now