Killing the DB :P

  • Some one please tell me the T SQl syntax of

    1-Disconnecting the Users of a DB

    2-Killing the sessions/Processes of a DB

    By da way i required this in order to drop the DB using the DROP command, and before that i want to make sure that no User is connected to the DB also there is no Session/Process of the DB

  • You could try:

    ALTER DATABASE myDb SET RESTRICTED_USER WITH NO_WAIT

    /Kenneth

  • I found a way....its the KILL statement.....KILL statement requires the SPID of the process to be killed, i wrote the following code to find the SPID's of my Database, but now the porblem is KILL is not working with the SPID in variable....i.e KILL works fine if we write

    KILL 23 (here 23 is a SPID)............ but it is not working if we write

    Declare @id int

    SET @id = 23

    KILL @id

    consider the following code...i m finding the SPID's of all process of my Database, then i use cursor to KILL those processes one by one.....but KILL is not working with SPID stored in Variable.

    begin

     create table #temptable (id int,ecid int, status varchar(200), login varchar(200), host varchar(200),blk int, db varchar(200), cmd varchar(500))

     insert into #temptable  exec SP_who

     DECLARE abc CURSOR for select id from #temptable where db = 'ADL'

     declare @id int

     open abc

     fetch next from abc into @id

     while @@fetch_status = 0

     begin

      KILL @id

      fetch next from abc into @id 

     end

     close abc

     deallocate abc

    end

  • You have to use EXEC('Kill ' + @spid) since 'KILL' won't accept a variable as parameter

    Though note that if you have quick users, there's nothing preventing them to reconnect immediately after you've killed them...

    /Kenneth

  • Something like this will work:

    DECLARE @spid_table TABLE (spid smallint)

    DECLARE @spid smallint,

     @kill nvarchar(11)

    INSERT INTO @spid_table

    SELECT spid FROM master..sysprocesses

    WHERE dbid=DB_ID(db_name()) AND spid <> @@spid

    WHILE (SELECT count(spid) FROM @spid_table) > 0

    BEGIN

     SET @spid = (SELECT TOP 1 spid FROM @spid_table)

     SET @kill = 'KILL '+cast(@spid as varchar)

     EXEC sp_executesql @stmt=@kill

     DELETE FROM @spid_table WHERE spid = @spid

    END

    Note that it excludes @@spid, as you can't kill your own connection.

  • You should also avoid the systemspids 1-7.

    /Kenneth

  • thanks every one who replied.....

    My solution works fine after the change suggested by Kenneth. i.e EXEC('Kill ' + @spid)

    Now about the system spids.....they will not be returned by the cursor...coz i m selecting the spids of only my DB.......

    Now about killing the own connection.......this script will be executed from an application installer.....so it has to kill all the connections of the DB, no matter who is connected with the DB.

    So i m getting the desired results...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply