Its frequently happens when we need to delete a database or restore a database and few users are still connected to that specific Database, it’s through an error saying that database is used by other users. Here is a script, using this we can drop/kill all connection to a specific database.

— Let Database name is VIRENDRATEST and we want to dropp all connection of this DB.

Use Master
Go 

Declare @dbname sysname
Set @dbname =
‘VIRENDRATEST’

–Write the database name that you want to drop connections

Declare @spid int
Select @spid =
min(spidfrom master.dbo.sysprocesses where dbid = db_id(@dbname)
While @spid is 
not NULL

Begin
Execute (‘Kill ‘ + @spid)
Select @spid
min(spidfrom master.dbo.sysprocesses where dbid =db_id(@dbnameand spid > @spid

End

Print ‘All connection DROPPED!’

Advertisements
Comments
  1. Tlhogi says:

    Nice!!

    Like

  2. Anders Bodilsen says:

    or you could do something like this:

    ALTER DATABASE [NAME_OF_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO

    OR
    ALTER DATABASE [NAME_OF_DB] SET MULTI_USER

    Like

  3. […] via Drop all connections of a database. […]

    Like

  4. Chetan says:

    I use this proc.

    CREATE PROCEDURE [dbo].[usp_Kill_DBconnections]
    (@arg_dbname varchar(128))

    AS

    SET NOCOUNT ON

    DECLARE @a_spid smallint
    DECLARE @msg varchar(255)
    DECLARE @a_dbid int

    –Original logic in this phrase
    If @arg_dbname IS NOT NULL

    BEGIN

    SELECT @a_dbid = sdb.dbid
    FROM master..sysdatabases sdb WITH (NOLOCK)
    WHERE sdb.name = @arg_dbname

    DECLARE db_users INSENSITIVE CURSOR FOR
    SELECT
    sp.spid
    FROM master..sysprocesses sp WITH (NOLOCK)
    CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle) as qt
    WHERE
    sp.spid > 50
    AND (sp.dbid = @a_dbid or qt.text like ‘%’+ @arg_dbname +’.%’)
    AND sp.spid @@SPID

    OPEN db_users

    FETCH NEXT FROM db_users INTO @a_spid
    WHILE @@fetch_status = 0
    BEGIN
    SELECT @msg = ‘kill ‘ + convert(char(5),@a_spid)
    begin try
    EXECUTE (@msg)
    end try

    begin catch
    print @@error
    end catch

    FETCH NEXT FROM db_users INTO @a_spid
    END

    CLOSE db_users
    DEALLOCATE db_users

    END

    ELSE — (@arg_dbname IS NULL) = Kill them all

    BEGIN

    DECLARE db_users INSENSITIVE CURSOR FOR
    SELECT
    sp.spid
    FROM master..sysprocesses sp WITH (NOLOCK)
    CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle) as qt
    WHERE
    sp.spid not in
    (
    select
    spid
    from master..sysprocesses WITH (NOLOCK)
    where (loginame like ‘%!%’
    OR loginame like ‘%SQL%’
    )
    )
    AND sp.spid > 50
    AND (sp.dbid = @a_dbid or qt.text like ‘%’+ @arg_dbname +’%’)
    AND sp.spid @@SPID

    OPEN db_users

    FETCH NEXT FROM db_users INTO @a_spid
    WHILE @@fetch_status = 0
    BEGIN
    SELECT @msg = ‘kill ‘ + convert(char(5),@a_spid)

    begin try
    EXECUTE (@msg)
    end try

    begin catch
    print @@error
    end catch

    FETCH NEXT FROM db_users INTO @a_spid

    END

    CLOSE db_users
    DEALLOCATE db_users
    –Waitfor Delay ’00:00:15’
    END

    Like

  5. Dineshtiwary says:

    HI Virender\chetan
    your script is nice but @chetan you mentioned SP>50 I think you a avoided the system !process but it is not true that system process is up to 50 it is not safe actually I need a script where we can delete the current connection of a database and it also execulde the system process that whose hostporcess !=” as you now those are blank are consider to be system process and at the same time we need to exclude the backup job /backup job might be in form of litespeed or native so we need a combination of all these if you have please share to me
    dineshtiwary25@gmail.com

    Like

  6. Dineshtiwary says:

    DECLARE @cmdKill VARCHAR(50)

    DECLARE killCursor CURSOR FOR
    SELECT ‘KILL ‘ + Convert(VARCHAR(5), p.spid)
    FROM master.dbo.sysprocesses AS p
    WHERE p.dbid = db_id(‘Dinesh’)
    and program_name not
    in(‘SLSSQLMaint – LiteSpeed engine’,’.Net SqlClient Data Provider’)and hostprocess!=”
    OPEN killCursor
    FETCH killCursor INTO @cmdKill

    WHILE 0 = @@fetch_status
    BEGIN
    EXECUTE (@cmdKill)
    FETCH killCursor INTO @cmdKill
    END

    CLOSE killCursor
    DEALLOCATE killCursor

    this is the script of all the three combination but still it is not up to the mark because backup is not included correctively native backup need to be included litespeed may be not up to be mark so please if any one please share to us I need this urgently………….

    Like

    • Hi Dinesh,
      The best greeting of the day Dear!

      I tried to chat with you on G-Talk, but hopefully you were not available that time.
      Hopefully you can get answer in sp_who2 script, you can get idea and you can exclude that particular programme or spids in your script.
      you can see sp_who2 script as USE Master; Go; sp_helptext sp_who2

      It will be very helpfull to your query.

      Thanks again for your valuable comments and waiting for your valuable replies.

      Thanking you,
      Virendra

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s