Session Killer for SQL Server

• Download script - 1.82 KB

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

----------------------------------------------------------------------------
-- Variable
----------------------------------------------------------------------------
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; -- Assign Seesion time in Minutes

-- Assign List of Users who will not be affected by this Session Kill
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;

    -- String together the KILL statement
    SELECT @command = 'KILL ' + CAST(@vSID AS varchar(5))

    BEGIN TRY

        -- Execute the final string to KILL the spids
        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

-- Assign Seesion time in Minutes

set @session_time=5; 

-- Assign List of Users who will not be affected by this Session Kill

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;

    -- String together the KILL statement
    SELECT @command = 'KILL ' + CAST(@vSID AS varchar(5))

    BEGIN TRY

        -- Execute the final string to KILL the spids
        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

• KILL (Transact-SQL) : http://technet.microsoft.com/en-us/library/ms173730.aspx

History

None so far.