August 22, 2011 at 4:12 pm
Hi
If i use the following command,
ALTER DATABASE [dbName]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
will it kill all the processes on the database and rollback transactions if required?
Or do i need to kill sysprocesses as a seperate statement before i set the database to single user?
I think it will do both kill processes and rollback transactions if required. Please confirm
August 22, 2011 at 5:55 pm
Anam Verma (8/22/2011)
HiIf i use the following command,
ALTER DATABASE [dbName]SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
will it kill all the processes on the database and rollback transactions if required?
Or do i need to kill sysprocesses as a seperate statement before i set the database to single user?
I think it will do both kill processes and rollback transactions if required. Please confirm
It will kill the processes and rollback all transaction before it sets the database in single user mode.
You might want to test.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 24, 2011 at 2:55 pm
Anam Verma (8/22/2011)
HiIf i use the following command,
ALTER DATABASE [dbName]SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
will it kill all the processes on the database and rollback transactions if required?
Yes. I would recommend doing it this way:
USE [dbName]
GO
ALTER DATABASE [dbName]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
This way your connection will be guaranteed to be the only user allowed to stay logged in.
Or do i need to kill sysprocesses as a seperate statement before i set the database to single user?
Nope. This is one of the great benefits of using the command.
I think it will do both kill processes and rollback transactions if required. Please confirm
Confirmed. It rolls all open transactions back and kicks everyone out all in one fell swoop, ensuring consistency.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply