KILL Process - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

KILL Process

Author

Topic

kfkenneth2001
Starting Member

12 Posts

Posted - 2005-05-06 : 10:50:21

I would like to kill all process that my client app using before making database restoration. I try to use the following script:
Declare @spid smallint
Set @spid=(select spid from sysprocesses where loginame='myAppOwner')
KILL @spid

But KILL does not accept the @spid and give error. How should I modify my script?

Also, it is more perfect if I can select @spid from the result of sp_who instead. How can I do this??

Thanks a lot.

Kennon

Sully
Starting Member

30 Posts

Posted - 2005-05-06 : 11:24:13

I got this from someone on this site, right now I can't remember who, but it works great.

declare @cmd varchar(4000)
declare @dbname sysname
declare @spid int

select @dbname = 'DBname'

declare curKill cursor forward_only
for
select
spid
from
master.dbo.sysprocesses a (nolock)
where
a.dbid = db_id(@dbname)
order by
a.spid

open curKill

fetch next from curKill into @spid

while (@@fetch_status = 0)
begin
select @cmd = 'Kill ' + str(@spid)
exec (@cmd)
fetch next from curKill into @spid
end

close curKill

deallocate curKill

Stuck in neutral in a pedal to the metal world,
Sully

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-06 : 12:08:35

ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Brett

8-)

kfkenneth2001
Starting Member

12 Posts

Posted - 2005-05-06 : 23:48:55

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources