Professional Web Applications Themes

Break all connections - Microsoft SQL / MS SQL Server

Hi , How can I break up all sql connections? I need this in order to restore a database after that. Thanks....

Sponsored Links
  1. #1

    Default Break all connections

    Hi ,

    How can I break up all sql connections?
    I need this in order to restore a database after that.

    Thanks.


    Sponsored Links
    Mircea Berghia Guest

  2. #2

    Default Re: Break all connections

    What version of SQL Server?

    For 2000:
    ALTER DATABASE <databasename> SET single_user WITH ROLLBACK IMMEDIATE

    For 7.0, you will need to run a cursor against the spids in sysprocesses,
    and use dynamic SQL to exec kill commands against each of them...




    "Mircea Berghia" <berghiaioanmircea> wrote in message
    news:#wJc6q7RDHA.1552TK2MSFTNGP10.phx.gbl...
    > Hi ,
    >
    > How can I break up all sql connections?
    > I need this in order to restore a database after that.
    >
    > Thanks.
    >
    >

    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: Break all connections

    P.S. Don't know why I didn't suggest this first... but if you really want to
    kill all the connections for the database...

    you might also consider using ALTER DATABSE to set the DB to single user
    mode. You can issue the

    WITH <termination> clause

    Specifies when to roll back incomplete transactions when the database is
    transitioned from one state to another. Only one termination clause can be
    specified and it follows the SET clauses.

    ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
    Specifies whether to roll back after the specified number of seconds or
    immediately. If the termination clause is omitted, transactions are allowed
    to commit or roll back on their own.

    NO_WAIT
    Specifies that if the requested database state or option change cannot
    complete immediately
    without waiting for transactions to commit or roll back on their own, the
    request will fail.

    This would be easier than doing what I described in my other post...

    --

    Brian Moran
    Principal Mentor
    Solid Quality Learning
    SQL Server MVP
    [url]http://www.solidqualitylearning.com[/url]


    "Mircea Berghia" <berghiaioanmircea> wrote in message
    news:#wJc6q7RDHA.1552TK2MSFTNGP10.phx.gbl...
    > Hi ,
    >
    > How can I break up all sql connections?
    > I need this in order to restore a database after that.
    >
    > Thanks.
    >
    >

    Brian Moran Guest

Similar Threads

  1. line break
    By cozzabags in forum Macromedia Contribute General Discussion
    Replies: 4
    Last Post: August 19th, 05:21 PM
  2. give me a break
    By eat_sh1t in forum Coldfusion Flash Integration
    Replies: 5
    Last Post: December 5th, 02:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139