killing a 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 2005 Forums

SQL Server Administration (2005)

killing a process

Author

Topic

thaniparthi.rao
Yak Posting Veteran

96 Posts

Posted - 2008-05-25 : 10:23:31

If we have a deadlock we will check in the error log and and find the spids which are involving in the deadlock.
We will kill one of the process by using SPID (no) KILL.
Is there specific steps to consider while killing a process

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-25 : 10:41:48

If you have enabled Trace flag -T1222(in 2005) or -T1204(in 2000) then you will be able to see what exactly occured ,process list and resource list of deadlock.Actually, it selects one Spid as victim, roll backs his transaction so other can continue.You should find out what exactly is causing deadlock like inappropriate indexes, high isolation level and etc. check books online for Detailed info.
Also you can DBCC inputbuffer(spid) and find exactly is running before killing but main point is you have find out what is causing.

mdubey
Posting Yak Master

133 Posts

Posted - 2008-05-25 : 12:14:08

You can use DBCC inputbuffer<SPID> run on Master DB. Probably if you need to kill any process before taking any action you may need to check that is that process is related to system or not. You can not KILL the process which is system used.

Please USE SP_WHO2 Active

It will give you active processes and also agian make sure you will kill the process releated to system.

Kill command is so easy.

KILL <SPID>

Or else if you want to KILL all the processes you can use this SP

Or else you can schedule a Job for the same.

Declare @DBName sysname
Declare @spid int
Declare @Qry varchar(255)

set @DBName = '<DBNAME>'
-- First loop to kill all connections to the database
Declare process_cursor cursor
FOR
select spid from master.dbo.sysprocesses
where db_name(dbid) = @DBName
and loginame not in('sa')
and spid <> @@spid

OPEN process_cursor
Fetch process_cursor into @spid
WHILE @@FETCH_STATUS = 0
BEGIN

select @Qry = 'Kill ' + convert(varchar(10), @spid)
--PRINT @Qry
exec(@Qry)
Fetch process_cursor into @spid
END

close process_cursor
Deallocate process_cursor

-- Until loop to kill all remaining/Unkilled connections
WHILE (select count(1) from master.dbo.sysprocesses
where db_name(dbid) = @DBName
and loginame not in('sa')
and spid <> @@spid
) > 0
BEGIN
select Top 1 @spid = spid from master.dbo.sysprocesses
where db_name(dbid) = @DBName
and loginame not in('sa')
and spid <> @@spid

select @Qry = 'Kill ' + convert(varchar(10), @spid)
--PRINT @Qry
exec(@Qry)
END

Manoj
MCP, MCTS

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-25 : 14:07:35

Sql server will kill least cost process involved in deadlock automatically.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources