Solved

single user mode in GUI

Posted on 2010-08-20
12
495 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
Comment Utility
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
Comment Utility
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
Comment Utility
could this a version issue? I am using standard 2005 SSMS.
0
 
LVL 6

Author Comment

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

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 6

Author Comment

by:anushahanna
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
sounds good. thanks for explaining that, Jim!

Enjoy your very bright day :-)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
table fragmentation 40 73
Choosing SSD drives for SQL Server 32 77
c# code 19 57
sql calculate averages 18 24
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

15 Experts available now in Live!

Get 1:1 Help Now