Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Killing the Connections

Posted on 1999-06-30
2
Medium Priority
?
234 Views
Last Modified: 2010-03-19
I have a stored procedures that automates a load of data.  I want to load this data during the day when connection volume is high.  How can I kill everyone's process in my stored procedure to ensure I won't get a deadlock before the load starts.
0
Comment
Question by:cards
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 7

Accepted Solution

by:
simonsabin earned 400 total points
ID: 1096046
The following SP will kill all processes on a particulat or all databases
if exists (select * from sysobjects where id = object_id('dbo.KILL_DATABASE') and sysstat & 0xf = 4)
      drop procedure dbo.KILL_DATABASE
GO

CREATE PROCEDURE KILL_DATABASE @database_name varchar(255) = "%"
AS
BEGIN

  DECLARE @pid smallint,@db_name varchar(255),@kill varchar(255)

  DECLARE c_users CURSOR FOR
      SELECT   DISTINCT db.name,p.spid
      FROM     sysprocesses p,sysdatabases db
      WHERE    p.dbid = db.dbid
      AND      hostname <> ""
      AND      UPPER(db.name) LIKE UPPER(@database_name)


    OPEN c_users
next_user:
    FETCH c_users INTO @db_name,@pid
    IF @@FETCH_STATUS <> -1
      BEGIN
      SELECT @kill = 'KILL '+ convert(varchar,@pid)
      print @kill
      EXECUTE (@kill)

      GOTO next_user
      END
    CLOSE c_users
    DEALLOCATE c_users


END

GO

It needs to be run from the master database or the reference to sysprocesses and sysdatabases need to be changed. It is a bit dangerous to use something of this nature. I use another SP to inform all users of a particular database to log off so that I can perform an update or restore.
0
 

Author Comment

by:cards
ID: 1096047
Thank you, this stored procedure will be very helpful.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question