Friday, March 22, 2019

Kill all connections to SQL Server database -- fast

What's the fastest way to kill all database connections?  Properly, I should say... without pulling the cord on the server.

I'm glad you asked! 😉  The easiest/fastest way that I am aware of is to set the database into SINGLE_USER.  This will sever all active connections to the database aside from your own.  Like this: 

-- kill all connections to a database
USE master;
ALTER DATABASE SSISDB -- change to your dbname
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE; 

When you've done your thing and you're ready to let them back in, you'll set it back to MULTI_USER, like this:

-- set it back into multi user
USE master;
ALTER DATABASE SSISDB -- change to your dbname
SET MULTI_USER;


Easy peasy.

You could also use a cursor to go through all active processes in the database, killing them sequentially... but, I don't often recommend the cursor method, if it can be avoided.

5 comments:

  1. You have provided a nice article, Thank you very much for this one. And I hope this will be useful for many people. And I am waiting for your next post keep on updating these kinds of knowledgeable things
    SEO Training in Chennai
    SEO Course in Chennai
    Advanced Java Training in Chennai
    Hadoop Course in Chennai
    Big Data Analytics Courses in Chennai
    Big Data Course in Chennai
    Selenium Training in Chennai
    SEO Training in Tambaram

    ReplyDelete
  2. Thank you all for the compliments! I will keep posting!

    ReplyDelete
  3. Really wonderful blog! Thanks for taking your valuable time to share this with us. Keep us updated with more such blogs.
    AWS Certification in Chennai
    AWS Online Training
    AWS Training in Coimbatore

    ReplyDelete