Loading ...
Sorry, an error occurred while loading the content.

Re: [mugh-sqlcon] Close connections on a database

Expand Messages
  • Santhosh
    Babu, If that is a cluster then take the ipaddress resource offline that is the fastest way to get rid of all the connection... with smiles santhosh ...
    Message 1 of 5 , Feb 3, 2006
    • 0 Attachment
      Babu,
      If that is a cluster then take the ipaddress resource
      offline that is the fastest way to get rid of all the
      connection...

      with smiles
      santhosh

      --- Preethiviraj Kulasingham
      <preethi_kulasingham@...> wrote:

      > What I do is this:
      > Select 'Kill '+convert(varchar, spid) from
      > sysprocesses where spid <> @@spid
      > -- <add condiiton if you want to filter for a db
      > or soem other category
      > then i get the result and execute it.
      >
      > If you want a faster result you can try with
      > sp_execresultset
      > 'Select ''Kill ''+convert(varchar, spid) from
      > sysprocesses where spid <> @@spid
      > -- additional conditions'
      >
      > Note that the word Kill is used within two single
      > quotes for the second statement
      >
      >
      > Enjoy
      >
      > Babu PP <babupp@...> wrote:
      > Hi All,
      >
      > Is there any option to close all users connections
      > to a particular database
      > (not like KILLing individual connections, because
      > there are so many
      > connections)
      >
      > Regards
      > Babu
      >
      >
      > [Non-text portions of this message have been
      > removed]
      >
      >
      >
      > SqlCon is a special interests group run under the
      > aegis of MUGH - http://www.mugh.net
      > http://www.mugh.net/sql
      >
      > Visit http://www.mugh.net/dnug to join .NET User
      > Group.
      >
      > Yahoo! Groups Links
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      > Best Regards,
      > G.R. Preethiviraj Kulasingham
      > Database Administrator
      > http://preethiviraj.blogspot.com/
      >
      >
      >
      >
      > ---------------------------------
      > Relax. Yahoo! Mail virus scanning helps detect nasty
      > viruses!
      >
      > [Non-text portions of this message have been
      > removed]
      >
      >


      __________________________________________________
      Do You Yahoo!?
      Tired of spam? Yahoo! Mail has the best spam protection around
      http://mail.yahoo.com
    • kuldeep chauhan
      The best way would be to bring the database in single user mode and then again set it back to multi user mode with rollback immediate. Here is the syatax for
      Message 2 of 5 , Feb 4, 2006
      • 0 Attachment
        The best way would be to bring the database in single user mode and then again set it back to multi user mode with rollback immediate. Here is the syatax for the same.
        Note: Run this script thru query analyzer from Master database (preffered)

        1. Alter Database Database_Name Set Single_User with Rollback Immediate
        (This query will kill all the existing connection and will rollback all the non-committed trancastions. This will also bring your database in single user mode)

        2. Alter Database Database_Name Set Multi_User with Rollback Immediate

        (This query will again set back your database to multi user mode).

        Thanks,
        Kuldeep

        Santhosh <san_spy@...> wrote:
        Babu,
        If that is a cluster then take the ipaddress resource
        offline that is the fastest way to get rid of all the
        connection...

        with smiles
        santhosh

        --- Preethiviraj Kulasingham
        <preethi_kulasingham@...> wrote:

        > What I do is this:
        > Select 'Kill '+convert(varchar, spid) from
        > sysprocesses where spid <> @@spid
        > -- <add condiiton if you want to filter for a db
        > or soem other category
        > then i get the result and execute it.
        >
        > If you want a faster result you can try with
        > sp_execresultset
        > 'Select ''Kill ''+convert(varchar, spid) from
        > sysprocesses where spid <> @@spid
        > -- additional conditions'
        >
        > Note that the word Kill is used within two single
        > quotes for the second statement
        >
        >
        > Enjoy
        >
        > Babu PP <babupp@...> wrote:
        > Hi All,
        >
        > Is there any option to close all users connections
        > to a particular database
        > (not like KILLing individual connections, because
        > there are so many
        > connections)
        >
        > Regards
        > Babu
        >
        >
        > [Non-text portions of this message have been
        > removed]
        >
        >
        >
        > SqlCon is a special interests group run under the
        > aegis of MUGH - http://www.mugh.net
        > http://www.mugh.net/sql
        >
        > Visit http://www.mugh.net/dnug to join .NET User
        > Group.
        >
        > Yahoo! Groups Links
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        > Best Regards,
        > G.R. Preethiviraj Kulasingham
        > Database Administrator
        > http://preethiviraj.blogspot.com/
        >
        >
        >
        >
        > ---------------------------------
        > Relax. Yahoo! Mail virus scanning helps detect nasty
        > viruses!
        >
        > [Non-text portions of this message have been
        > removed]
        >
        >


        __________________________________________________
        Do You Yahoo!?
        Tired of spam? Yahoo! Mail has the best spam protection around
        http://mail.yahoo.com


        SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User Group.




        SPONSORED LINKS
        C programming language Computer programming languages Java programming language The c programming language C programming language Concept of programming language

        ---------------------------------
        YAHOO! GROUPS LINKS


        Visit your group "sqlcon" on the web.

        To unsubscribe from this group, send an email to:
        sqlcon-unsubscribe@yahoogroups.com

        Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


        ---------------------------------





        ---------------------------------
        Yahoo! Mail - Helps protect you from nasty viruses.

        [Non-text portions of this message have been removed]
      • smart chip
        Taking the resources to offline is not a good option becasue it kills all other databases connectiones too. The best way is of kuldeep s one. Alter database
        Message 3 of 5 , Feb 4, 2006
        • 0 Attachment
          Taking the resources to offline is not a good option becasue it kills all other databases connectiones too. The best way is of kuldeep's one. Alter database with roll back immediate.

          kuldeep chauhan <mrkuldeepchauhan@...> wrote: The best way would be to bring the database in single user mode and then again set it back to multi user mode with rollback immediate. Here is the syatax for the same.
          Note: Run this script thru query analyzer from Master database (preffered)

          1. Alter Database Database_Name Set Single_User with Rollback Immediate
          (This query will kill all the existing connection and will rollback all the non-committed trancastions. This will also bring your database in single user mode)

          2. Alter Database Database_Name Set Multi_User with Rollback Immediate

          (This query will again set back your database to multi user mode).

          Thanks,
          Kuldeep

          Santhosh <san_spy@...> wrote:
          Babu,
          If that is a cluster then take the ipaddress resource
          offline that is the fastest way to get rid of all the
          connection...

          with smiles
          santhosh

          --- Preethiviraj Kulasingham
          <preethi_kulasingham@...> wrote:

          > What I do is this:
          > Select 'Kill '+convert(varchar, spid) from
          > sysprocesses where spid <> @@spid
          > -- <add condiiton if you want to filter for a db
          > or soem other category
          > then i get the result and execute it.
          >
          > If you want a faster result you can try with
          > sp_execresultset
          > 'Select ''Kill ''+convert(varchar, spid) from
          > sysprocesses where spid <> @@spid
          > -- additional conditions'
          >
          > Note that the word Kill is used within two single
          > quotes for the second statement
          >
          >
          > Enjoy
          >
          > Babu PP <babupp@...> wrote:
          > Hi All,
          >
          > Is there any option to close all users connections
          > to a particular database
          > (not like KILLing individual connections, because
          > there are so many
          > connections)
          >
          > Regards
          > Babu
          >
          >
          > [Non-text portions of this message have been
          > removed]
          >
          >
          >
          > SqlCon is a special interests group run under the
          > aegis of MUGH - http://www.mugh.net
          > http://www.mugh.net/sql
          >
          > Visit http://www.mugh.net/dnug to join .NET User
          > Group.
          >
          > Yahoo! Groups Links
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          > Best Regards,
          > G.R. Preethiviraj Kulasingham
          > Database Administrator
          > http://preethiviraj.blogspot.com/
          >
          >
          >
          >
          > ---------------------------------
          > Relax. Yahoo! Mail virus scanning helps detect nasty
          > viruses!
          >
          > [Non-text portions of this message have been
          > removed]
          >
          >


          __________________________________________________
          Do You Yahoo!?
          Tired of spam? Yahoo! Mail has the best spam protection around
          http://mail.yahoo.com


          SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User Group.




          SPONSORED LINKS
          C programming language Computer programming languages Java programming language The c programming language C programming language Concept of programming language

          ---------------------------------
          YAHOO! GROUPS LINKS


          Visit your group "sqlcon" on the web.

          To unsubscribe from this group, send an email to:
          sqlcon-unsubscribe@yahoogroups.com

          Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


          ---------------------------------





          ---------------------------------
          Yahoo! Mail - Helps protect you from nasty viruses.

          [Non-text portions of this message have been removed]



          SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
          http://www.mugh.net/sql

          Visit http://www.mugh.net/dnug to join .NET User Group.




          SPONSORED LINKS
          C programming language Computer programming languages Java programming language The c programming language C programming language Concept of programming language

          ---------------------------------
          YAHOO! GROUPS LINKS


          Visit your group "sqlcon" on the web.

          To unsubscribe from this group, send an email to:
          sqlcon-unsubscribe@yahoogroups.com

          Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


          ---------------------------------





          ---------------------------------
          Brings words and photos together (easily) with
          PhotoMail - it's free and works with Yahoo! Mail.

          [Non-text portions of this message have been removed]
        Your message has been successfully submitted and would be delivered to recipients shortly.