Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

single user mode in GUI

Posted on 2010-08-20
12
498 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:
Scott Pletcher 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 6

Author Comment

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

Expert Comment

by:Scott Pletcher
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
 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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…
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…
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

792 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