• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

single user mode in GUI

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
anushahanna
Asked:
anushahanna
  • 6
  • 2
  • 2
  • +1
5 Solutions
 
Scott PletcherSenior DBACommented:
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
 
anushahannaAuthor Commented:
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
 
anushahannaAuthor Commented:
could this a version issue? I am using standard 2005 SSMS.
0
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.

 
anushahannaAuthor Commented:
i tried this in enterprise 2005 also, but same issue.
0
 
Scott PletcherSenior DBACommented:
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
 
mastooCommented:
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
 
anushahannaAuthor Commented:
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
 
mastooCommented:
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
 
Jim P.Commented:
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
 
anushahannaAuthor Commented:
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
 
Jim P.Commented:
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
 
anushahannaAuthor Commented:
sounds good. thanks for explaining that, Jim!

Enjoy your very bright day :-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now