Killing DB Sessions - 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

SQL Server Administration (2000)

Killing DB Sessions

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-10-18 : 10:03:42

Guys,

Is there any query by which I can kill all the sessions coming out a database, user, hostname etc.

Actually I am looking for a query that can kill the sessions by the columns populated in 'Process Info' tab of Enterprise manager.

Right now I am able to kill only a single seesion by

KILL {spid | UOW} [WITH STATUSONLY]
GO

Any suggestions/ideas would help in this regard

Thanks

supersql
Yak Posting Veteran

99 Posts

Posted - 2005-10-18 : 10:10:48

Yup u can do that, just run this SP it just kills all the processess.
--------------------------------------------------------------------------
create proc kill_db_processes
(@dbname varchar(20))
as

Declare @dbid int,
@spid int,
@str nvarchar(128)
select @dbid = dbid from master..sysdatabases
where name = @dbname
declare spidcurs cursor for
select spid from master..sysprocesses where dbid = @dbid
open spidcurs
fetch next from spidcurs into @spid
While @@fetch_status = 0
Begin
Select @str = 'Kill '+convert(nvarchar(30),@spid)
exec(@str)
--print @str
fetch next from spidcurs into @spid
End
Deallocate spidcurs

GO
----------------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources