Solved

single user mode in GUI

Posted on 2010-08-20
12
501 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
[X]
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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 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