Introduction
If you want to close your database session for a certain interval then you can use this script.
Background
Why do we need a session killer at all? For my recent project it was necessary mostly for two reasons.
We are using more than 100 PDAs for medical record bed side data entry. Users like to leave
the PDA in login state.
We use Microsoft Dynamic NAV for our hospital management system. It is a desktop application so every connection is separate.
And the number of connections is important because of licensing concerns. So we need a session killer with
a pre defined time interval.
What you need
This script was tested in SQL Server 2005 and SQL Server 2008.
Main script
DECLARE @Session TABLE
(
id INT IDENTITY(1,1) PRIMARY KEY,
[sid] int,
ltime DATETIME,
hname varchar(MAX),
uid varchar(100),
lret datetime,
etime int
);
DECLARE @NotAffectedUserList TABLE
(
id INT IDENTITY(1,1) PRIMARY KEY,
[uid] varchar(MAX)
);
DECLARE @session_time as int;
DECLARE @vSID as int;
DECLARE @command as varchar(MAX);
DECLARE @RowIndex AS INT;
DECLARE @MaxIndex AS INT;
set @session_time=5;
INSERT into @NotAffectedUserList SELECT 'sa'
INSERT into @NotAffectedUserList SELECT 'scheduler'
INSERT into @Session
SELECT
session_id as [Session ID],
login_time as [Login Time],
host_name as [Host Name],
LOGIN_NAME as [Login ID],
last_request_end_time as [Last Request End Time],
DATEDIFF(minute, last_request_end_time,getdate()) as[Elapsed Time (minutes)]
FROM
sys.dm_exec_sessions
WHERE
host_name is not NULL
AND
client_version=4
AND DATEDIFF(minute, last_request_end_time,getdate())>=@session_time
AND LOGIN_NAME not in
(
SELECT DISTINCT uid
FROM @NotAffectedUserList
)
SET @RowIndex = 1;
SELECT @MaxIndex = MAX(id) FROM @Session;
WHILE( @RowIndex <= @MaxIndex)
BEGIN
SELECT @vSID = sid
FROM @Session
WHERE id = @RowIndex;
SELECT @command = 'KILL ' + CAST(@vSID AS varchar(5))
BEGIN TRY
EXEC (@command)
END TRY
BEGIN CATCH
declare @emsg varchar(500);
SELECT
@emsg=ERROR_MESSAGE();
print @emsg;
END CATCH
SET @RowIndex = @RowIndex + 1;
END;
Analysis script - Initial variable
set @session_time=5;
insert into @NotAffectedUserList select 'sa'
insert into @NotAffectedUserList select 'scheduler'
Put your desired session expire value in the @session_time variable and add as many users in @NotAffectedUserList whom you want to exclude
from your session kill.
Analysis script - Load session status
INSERT into @Session
SELECT
session_id as [Session ID],
login_time as [Login Time],
host_name as [Host Name],
LOGIN_NAME as [Login ID],
last_request_end_time as [Last Request End Time],
DATEDIFF(minute, last_request_end_time,getdate()) as[Elapsed Time (minutes)]
FROM
sys.dm_exec_sessions
WHERE
host_name is not NULL
AND
client_version=4
AND DATEDIFF(minute, last_request_end_time,getdate())>=@session_time
AND LOGIN_NAME not in
(
SELECT DISTINCT uid
FROM @NotAffectedUserList
)
Load all sessions where session time is greater than your desired session
time and exclude those user IDs whom you don't want to expire.
It's possible to add some more flexibility against host names. You can set one value for one group.
Analysis script - Kill session
SET @RowIndex = 1;
SELECT @MaxIndex = MAX(id) FROM @Session;
WHILE( @RowIndex <= @MaxIndex)
BEGIN
SELECT @vSID = sid
FROM @Session
WHERE id = @RowIndex;
SELECT @command = 'KILL ' + CAST(@vSID AS varchar(5))
BEGIN TRY
EXEC (@command)
END TRY
BEGIN CATCH
declare @emsg varchar(500);
SELECT
@emsg=ERROR_MESSAGE();
print @emsg;
END CATCH
SET @RowIndex = @RowIndex + 1;
END;
After filtering your criteria it's time to kill the session. You can maintain a log when a session is expired.
You can use KILL session ID WITH STATUSONLY to obtain a progress report.
Create a schedule
Go to SQL Server Agent-> Jobs.
From General tab, Add New Jobs
From Step tab, Add New Step
From Schedule tab, Add New Schedule
Start Job at Step.
After 30 minutes, you will see the history of the job:
References
History
None so far.
