how to restart or take db in single user mode by hounering all requests.

Question

how to restart or take db in single user mode by hounering all requests.

rajemessage 14195

Hall of Fame

Points: 3315
More actions
October 10, 2015 at 7:59 am

#321440

hi,
some times i need to take db in single user mode , which can be done
using following
use master
go
alter database db_name set single_user with rollback immediate
go
this has other options but each one has some problems like if any single window is open at any location
then i have to personally ask to close all connections.
Q1) The need is i want to honor running transaction from "msss" or any application then i want db to get into single mode so that i can perform my task.
some body suggested me to shutdown then restart in single mode it may do, please confirm.
yours sincelery

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 1

rajemessage 14195 (10/10/2015)
hi,
some times i need to take db in single user mode , which can be done
using following
use master
go
alter database db_name set single_user with rollback immediate
go
this has other options but each one has some problems like if any single window is open at any location
then i have to personally ask to close all connections.
Q1) The need is i want to honor running transaction from "msss" or any application then i want db to get into single mode so that i can perform my task.
some body suggested me to shutdown then restart in single mode it may do, please confirm.
yours sincelery

The only way to do that is to remove the WITH ROLLBACK IMMEDIATE and you've already seen what happens when you do that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 2

rajemessage 14195 (10/10/2015)
hi,
some times i need to take db in single user mode , which can be done
using following
use master
go
alter database db_name set single_user with rollback immediate
go
this has other options but each one has some problems like if any single window is open at any location
then i have to personally ask to close all connections.
Q1) The need is i want to honor running transaction from "msss" or any application then i want db to get into single mode so that i can perform my task.
some body suggested me to shutdown then restart in single mode it may do, please confirm.
yours sincelery

if your own connection is in using yourdb instead of master, then you,and only you are in the database.

i tend to like to take the database offline instead of single user, because typically i'm going to restore over the top of it, and not necessarily run scripts that i want to guarantee noone is in the db at the same time.

use db_name

go

alter database db_name set single_user with rollback immediate

go

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!