Home Forums SQL Server 2008 SQL Server 2008 Administration how to restart or take db in single user mode by hounering all requests. RE: how to restart or take db in single user mode by hounering all requests.

  • October 10, 2015 at 1:42 pm

    #1832690

    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)