Web design and hosting, database, cloud and social media solutions that deliver real business results
  • Business Solutions
    • Robotic Process Automation
    • Bespoke Software
    • Database Services
      • Data Integration
      • Datawarehouse Services
      • Power BI
    • Web Services
      • Logo Design
      • Payment Gateways
      • Web Site Optimisation
      • Web Site Security
      • Technical Tools
    • Cloud Services
      • Amazon Web Services
      • Google Cloud Services
      • Microsoft Azure
    • Microsoft Office
    • Social Media Services
  • Academy
    • Our Test Environment
    • Learning Databases
      • The Basics
      • Get Open Query
      • SQL Server Data
      • SQL Server Maintenance
      • Using SQL Server Dates
      • Using SQL Server Functions
      • Using SQL Server Pivot-Unpivot
      • Technical Tools
    • Learning Web Design
      • Building Ousia Content Management System
      • Using ASP-NET
      • Using CSS
      • Using JavaScript
    • Learning Cloud and IT Services
      • Task Scheduler Error 2147943645
      • Requesting SSL and Generation of PFX file in OpenSSL Simple Steps
    • Using Social Media
      • Asking for a Google Review
      • Changing a Facebook account from personal to business
      • Choosing where to focus Social Media effort
      • Social Media Image Sizes
      • Using Meta Data to set Social Media Images
  • About
    • Blog
      • Building an entry level gaming machine
      • Google Core Update Jan 2020
      • Hot Chilli Internet Closure
      • How To Choose Content For Your Website Adverts Leaflets
      • Preventing Online Scam
      • Skimmers of the gig economy
      • The most annoying things about websites on the Internet
      • Top 5 websites for free Vector Graphics
    • Portfolio
    • Team
      • Adrian Anandan
      • Ali Al Amine
      • Ayse Hur
      • Chester Copperpot
      • Fernando Ancona Camara
      • Gavin Clayton
      • Sai Gangu
      • Suneel Kumar
      • Surya Mukkamala
      • Trevor Momanyi
AR - عربىCS - češtinaDE - DeutschES - EspañolFA - فارسیFR - FrançaisHI - हिंदीIT - italianoJA - 日本語PL - polskiPT - PortuguêsRU - русскийTR - TürkZH - 中国的

Stored Procedure to kill all connections to selected database or server

SQL Stored Procedure to kill all connections to selected database or server except for the process calling it.

Introduction

There can be times when you need to disconnect all users from your database, one example is for Log Shipping replication. This code below was used on a reporting server which was updated every 20 minutes, as the database needs to be opened in exclusive mode by the restore task.

The code below will accept a database name, so it only needs to be written once and stored on the master or utilities (if you have created one) database. It can then be called from any other process, in our case the complete maintenance plan in the document above this.

It is a fairly simple stored procedures that simply gets a list of the active connections and loops through each one killing the process and connection.

SQL Code

CREATE PROC KillConnections(@database VARCHAR(50))AS BEGINSET NOCOUNT ON;DECLARE @spid INTDECLARE @killstatement NVARCHAR(10)--Declare a cursor to select the users connected to the specified databaseDECLARE c1 CURSOR FAST_FORWARD FOR SELECT request_session_id                    FROM sys.dm_tran_locksWHERE resource_type='DATABASE'AND(DB_NAME(resource_database_id)=@database OR @database IS NULL)OPEN c1FETCH c1 INTO @spidWHILE @@FETCH_STATUS=0 BEGIN     IF @@SPID<>@spid--Don't kill the connection of the user executing this statement     BEGIN           -- Construct dynamic sql to kill spid           SET @killstatement='KILL '+CAST(@spid AS VARCHAR(5))           EXEC sp_executesql @killstatement           PRINT @spid-- Print killed spid                 END     FETCH NEXT FROM c1 INTO @spidEND-- Clean upCLOSE c1DEALLOCATE c1ENDGO

Author

Gavin Clayton
Gavin Clayton
I formed Claytabase in 2010 as a way of carrying on my work with SQL Server and ASP.NET. This has culminated in the Ousia Content Management System being taken from concept to one of the quickest CMS's on the market.

Was this helpful?

Please note, this commenting system is still in final testing.
Copyright Claytabase Ltd 2021, registered in England and Wales 08985867

Site Links

RSS Login ContactCookie PolicySitemap

Social Media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in Touch

+442392064871info@claytabase.co.ukGround Floor, Building 1000, Lakeside North Harbour, Western Road, Portsmouth, Hampshire, United Kingdom, PO6 3EZ

Partnered With

Partners
The settings on this site are set to allow all cookies. These can be changed on our Cookie Policy & Settings page.
By continuing to use this site you agree to the use of cookies.
Ousia Logo
Ousia CMS Loader