SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 singler_mode ASAP ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

loiter99
Starting Member

38 Posts

Posted - 06/18/2004 :  15:27:48  Show Profile  Reply with Quote
Question:

How do you get a DB in single user mode immediately?

Say a restore needs done, but you have users popping in and out and you need exclusive access to do a restore. The alter database command keeps failing because said access can't be obtained. What do you do?

Thanks in advance

J

tkizer
Almighty SQL Goddess

USA
36942 Posts

Posted - 06/18/2004 :  15:29:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run this before the RESTORE:

ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE


Tara
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/19/2004 :  02:43:53  Show Profile  Reply with Quote
Amazing how much I learn lurking around in here ...

Does that get the current task the single user connection, or might someone else grab it?

I'm meaning, can I do something like:

ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Some error check here?? e.g. loop round until success
RESTORE ....
GO

and be sure that this will run atomically with all other users locked out?

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36942 Posts

Posted - 06/20/2004 :  02:30:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
I've never had any users slip in with this command. I think the ROLLBACK IMMEDIATE almost guarantees that you get the single user connection. But I said almost, so there's still a possibility of someone slipping in.

Tara
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/20/2004 :  06:53:47  Show Profile  Reply with Quote
Well, now I think about it, the RESTORE will just fail if someone else manages to slip in, so I can check for that too. But at least it gives me the wherewithall to try to get exclusive use before the RESTORE starts

Thanks

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000