Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database
> before daily restore job in DR machine. >
> For testing, the script is getting the spid from sysprocesses and
> sysdatabases and store the specific spid which accessing the user DB
> into cursor and sp_who spid. You're making this way harder than it has to be: USE master GO ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!