KILL inactive users

SBokhorst

Because active users are quite expensive, I prefer to kill sessions of idle users
I first log the user activity.
I only kill sessions when they are close to our limmit of 33 concurrent users.
and because some users are working with batches that may not be killed we can exlude them..
and the script is limmitted to 5 kill actions.
I start the script with a maintanace plan but it can be started manualy as well

Regards.

USE master
GO

INSERT INTO USDB_MDNAV40SP3_D.dbo.ActivityUserMonitor
SELECT GETDATE() as now, spid, loginame, lastwaittype, dbid, uid, login_time, last_batch, open_tran, status, hostname, Program_name, Hostprocess, cmd, net_library, (GETDATE()-last_batch) AS inactivetime from sys.sysprocesses
WHERE --(GETDATE()-last_batch)> '1900-01-01 00:30:00.000' AND –- LAATSTE STUKJE KUN JE AAN ZETTEN DAN LOG JE ALLEEN DE MENSEN LANGER DAN 30 MIN.
net_library = 'TCP/IP' AND loginame <> 'bpmone' AND loginame <> 'user.to_exclude'
order by inactivetime
GO

IF ((SELECT COUNT(*) FROM sys.sysprocesses WHERE net_library = 'TCP/IP' AND loginame <> 'user.to_exclude' AND loginame <> 'user.to_exclude') > '28')
BEGIN

DECLARE @tokill smallint;
DECLARE @counter int;
DECLARE @CMD1 varchar(80)

SET @counter = 1


WHILE(SELECT count(*) from sysprocesses
WHERE (GETDATE()-last_batch)> '1900-01-01 00:30:00.000'
AND net_library = 'TCP/IP'
AND loginame <> 'user.to_exclude1'
AND loginame <> 'user.to_exclude2'
AND loginame <> 'user.to_exclude3'
AND loginame <> 'user.to_exclude4'
AND loginame <> 'user.to_exclude5'
OR
(GETDATE()-last_batch)> '1900-01-01 00:45:00.000'
AND net_library = 'TCP/IP'
AND loginame <> 'user.to_exclude1'
AND loginame <> 'user.to_exclude2'
AND loginame <> 'user.to_exclude3'
AND loginame <> 'user.to_exclude4') > 0 AND @counter < 5
BEGIN
SET @counter = @counter + 1 ;
SET @tokill = (SELECT max(spid) from sysprocesses
WHERE (GETDATE()-last_batch)> '1900-01-01 00:30:00.000'
AND net_library = 'TCP/IP'
AND loginame <> 'user.to_exclude1'
AND loginame <> 'user.to_exclude2'
AND loginame <> 'user.to_exclude3'
AND loginame <> 'user.to_exclude4'
AND loginame <> 'user.to_exclude5'
OR
(GETDATE()-last_batch)> '1900-01-01 00:45:00.000'
AND net_library = 'TCP/IP'
AND loginame <> 'user.to_exclude1'
AND loginame <> 'user.to_exclude2'
AND loginame <> 'user.to_exclude3'
AND loginame <> 'user.to_exclude4') ;

SET @CMD1 = 'KILL ' + CAST(@tokill AS varchar(5))

PRINT @CMD1
EXEC (@CMD1)
WAITFOR DELAY '00:00:20';

CONTINUE
END
END
ELSE
PRINT 'ONDER DE 28'