August 29, 2003 at 7:38 am
The problem I have with this answer is that it says "everyone" will be disconnected. That is not true. The issuer of the command is still connected. Therefore "everyone" is not disconnected that is why I chose answer (1) DB will be placed in single user mode after 30 seconds.
Users are still able to execute queries for the next 30 seconds.
Brian
August 29, 2003 at 8:13 am
I concur with bhaberman. Not all users are disconnected. Only unqualified users are.
August 29, 2003 at 9:07 am
True - it should read: everyone EXCEPT the issuer of the command. On the other point I think bhaberman is wrong: existing connections will be broken & transactions rolled back in 30 seconds. During that 30 second transition, users are not able to execute queries. It's only existing queries that are executed.
Data: Easy to spill, hard to clean up!
August 29, 2003 at 9:28 am
the so called correct answer todays question implies that by adding 'WITH ROLLBACK AFTER 30 SECONDS' means you will be disconneted in 30 secs. this is wrong the database automatically goes into single user mode but gives exsisting queries 30 seconds to complete else they are rolled back.
August 29, 2003 at 11:07 am
I chose answer 1 and was surprised to get it wrong. Surprised because I tested it first by opening 2 query analyzer windows. With window one I set Northwind to single user mode with rollback after 30 seconds. Then in window two I was able to run
Use Northwind
Select * from customers
for 30 seconds. After 30 seconds I get an error message that Northwind is already open and can have only one user at a time.
I go back to window one from which I set Northwind in single user mode and run
Use Northwind
Select * from customers
which of course returned all the rows from the customers table. Obviously not "everyone" was disconnected.
I would like my score card altered please to reflect that I answered this question correctly.
Dagoboz
August 29, 2003 at 12:33 pm
The answer appears to be incorrect.
In one connection, do this:
----------------------------------------
alter database northwind
set single_user
with rollback after 30 seconds
----------------------------------------
In another connection (different user) do this:
----------------------------------------
USE NORTHWIND
GO
SELECT * FROM employees
----------------------------------------
When the first statement is run, the user is no longer able to run his query in the second connection. However his connection still appears to *exist* in the database (looking at sp_who2), and works just fine after the database is set back to MULTI_USER. No message that he was *disconnected* from the database, just that it was in single user mode.
I know this is not what BOL says ("SINGLE_USER allows one user at a time to connect to the database. All other user connections are broken"), but it appears to be what happens.
John Scarborough
MCDBA, MCSA
September 1, 2003 at 2:35 am
This comment isn't directly related to the question, but it may also be worth noting that if you set a database in single user mode using ROLLBACK and you are not already USEing (sic) the database, then there is a chance that another user can USE the database before you do.
For this reason, I always do:
USE database
ALTER database SET single_user WITH ROLLBACK IMMEDIATE
GO
--
Si Chan
Database Administrator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply