Hi all,

Is there any ways I can write a program to Auto kill those
non active process in my SQL2000 server, those unwanted
process is killing our SQL performance.

You can schedule a job to run the following script on a regular basis. The
example kills all connections that have not been used for 6 hours:

DECLARE @sql varchar(4000)
WHILE 1=1
BEGIN
SET @sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
AND spid <> @@spid AND spid >= 50)
IF @sql IS NULL BREAK
EXEC (@sql)
END

It is ofcourse better if the application that opens those connections
disconnects them when it is done with them, but then I have once worked with
a programmer whose idea of connection pooling was to open 100 connection on
application startup, even though the app never used more than 2, so I know
you can be up against ;-)

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


[quoted text, click to view]